Seprate text and numbers within a cell

[needXL]

Dear All,

I just need a help again, i have a data in a cell and i just want to seprate all data , i have attached a sample workbook which have clarification exactly what i looking for.

thanks in advance.

 

[Hui]

B3: =LEFT(A3,FIND(".",A3)-1)
C3: =MID(A3,LEN(B3)+2,FIND(",",A3)-LEN(B3)-2)
D3: =MID(A3,FIND(",",A3)+2,FIND(" ",A3,FIND(", ",A3)+1)-LEN(B3&C3))
E3: =MID(A3,LEN(B3&C3&D3)+4,FIND("(",A3)-LEN(B3&C3&D3)-4)
F3: =RIGHT(A3,LEN(A3)-FIND("(",A3)+1)

copy down

 

[needXL]

THANk you sir,

but for

D3: =MID(A3,FIND(",",A3)+2,FIND(" ",A3,FIND(", ",A3)+1)-LEN(B3&C3))=
GA 2
instead of only GA

&

E3: =MID(A3,LEN(B3&C3&D3)+4,FIND("(",A3)-LEN(B3&C3&D3)-4)=
A 23122
instead of 23122

 

[NARAYANK991]

Hi ,

I assume that the State , Zip and Phone sections have fixed lengths.

If this is so , then the formulae for D3 , E3 and F3 can be simplified to :

D3 - =LEFT(RIGHT(A3,23),2)

E3 - =LEFT(RIGHT(A3,20),5)+0

F3 - =RIGHT(A3,14)

Narayan

 

[needXL]

Hi ,

I assume that the State , Zip and Phone sections have fixed lengths.

If this is so , then the formulae for D3 , E3 and F3 can be simplified to :

D3 - =LEFT(RIGHT(A3,23),2)

E3 - =LEFT(RIGHT(A3,20),5)+0

F3 - =RIGHT(A3,14)

Narayan

thanks narayan sir,

yes you are right and also formula working fine for me.

thanks again for your time and suggestions (BOTH sir's)