Extract city, state and zip code from a single cell

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

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



<jajoseph@zoominternet.net> wrote in message news:1107674941.810942.162640@c13g2000cwb.googlegroups.com...
> is there a way to Extract city, state and zip code from a single cell
>
> sample data:
>
> 4076 St. Andrews Ct. Canfield, OH 44406
>

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

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@zoominternet.net> wrote in message
news:1107707119.371429.79870@l41g2000cwc.googlegroups.com...
> Is there a way to do it without using Text to Columns in the menu ba
>

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.

In article <1107707119.371429.79870@l41g2000cwc.googlegroups.com>,
jajoseph@zoominternet.net wrote:

> 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!

Thank you this did the trick.