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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup with multiple fields that need to match 1
How to perform lookups based on multiple criteria? 4
Extract address data 2
Following in one cell. Need separate cells DE SOTO, TX, 75115, US 3
Filter by typed value 5
Separate colum 2
Finding the number of times a word is used in a range of cells 8
Formula anomaly 7

Back
Top