Extract city, state and zip code from a single cell

[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

 

[Ron de Bruin]

Hi

Try to use Data> Text to Columns in the menu bar

 

[jajoseph]

Is there a way to do it without using Text to Columns in the menu ba

 

[JulieD]

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

 

[jajoseph]

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.

 

[Domenic]

Is there a way to do it without using Text to Columns in the menu ba

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!

 

[jajoseph]

Thank you this did the trick.