Separate Text - Formula

D

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)
 
J

Jason Morin

For the city, extract everything left of the comma:

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

HTH
Jason
Atlanta, GA
 
D

Domenic

Hi,

Try,

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

Hope this helps!
 
K

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.
 
Top