Separate Text - Formula

  • Thread starter Thread starter Da
  • Start date Start date
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)
 
For the city, extract everything left of the comma:

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

HTH
Jason
Atlanta, GA
 
Hi,

Try,

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

Hope this helps!
 
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.
 
Back
Top