Separate Text - Formula

[Da]

Please help me with the formula to separate text. In the
formula below, it works if the city is ONE WORD. However,
when the city has two words, i.e., El Paso, the 'LEFT' and
the 'MID' formulas does not work.

I can't use the Data > Text to Columns feature because if
I check space, it will separate the name of the city (if
two names). If I do not check the space, the State and the
Zip Code will be in one cell.

Please HELP!


Dallas, TX 11111

City: =LEFT(A1,FIND(" ",A1,1)-2)
State: =MID(A1,FIND(" ",A1)+1,2)
Zip Code: =RIGHT(A1,5)

 

[Guest]

Instead of doing a find on the " ", try doing a find on
the comma.

 

[Jason Morin]

For the city, extract everything left of the comma:

=LEFT(A1,FIND(",",A1)-1)

HTH
Jason
Atlanta, GA

 

[Domenic]

Hi,

Try,

City: =LEFT(A1,FIND(",",A1)-1)
State: =MID(A1,FIND(",",A1)+2,2)

Hope this helps!

 

[Ken Wright]

Can't you use text To Columns and use comma as the separator as per your
example. This gets City in the first column and State / Zip in the second. Now
use text To Columns on Column 2 and use Space as the separator.

 

[Da]

Thanks Jason. How about the MID function. Thanks again.

 

[Da]

GOT IT! Thank you guys!

=MID(A1,FIND(",",A1)+2,2)