Extract city, state and zip code from a single cell

  • Thread starter Thread starter jajoseph
  • Start date Start date
J

jajoseph

is there a way to Extract city, state and zip code from a single cell

sample data:

4076 St. Andrews Ct. Canfield, OH 44406
 
hi

any reason why you don't want to use text to columns as it would probably be
the easiest option - are you looking for formulas or a vba solution?

Cheers
JulieD
 
I would prefer a formula since I am doing this in excel. I want this
to occur automatically, that is I why I do not want to use text to
cloumns.
 
Is there a way to do it without using Text to Columns in the menu ba
Click to expand...

For the following format...

New York, NY 012345

....try the following...

City: =LEFT(A1,SEARCH(",",A1)-1)

State: =MID(A1,SEARCH(",",A1)+2,2)

Zip Code:

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255)

....confirmed with CONTROL+SHIFT+ENTER.

For the following format...

New York, New York 012345

....replace the formula for State with the following...

=MID(A5,SEARCH(",",A1)+2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"
))-2-(SEARCH(",",A1)+1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Back
Top