need City, State Zip in 3 columns (Excel) 2

[azoe]

I've been given an Excel spreadsheet with the city, state zip in one column and I need 3 columns. One with city(no comma) one with state and one with zip.

It looks like most of the zip codes are 5 rather than 9 digits. So I can get those with the Left function, but I don't know how to get the state and then the city.

Since I have to choose the beginning character for the Mid function and I can't be sure which one that is.

There are 2 thousand of these so I really appreciate your help.

 

[xlhelp]

If for example you have Lolita, Texas, 77971, then lolita would be
=left(A2,find(",",a2)-1)

Texas would be
=MID(A2,FIND(",",A2)+1,FIND(",",A2,FIND(",",A2)+1)-FIND(",",A2)-1)

Depending on spaces you will need to play with plus 1's and -1

And Zip code

=RIGHT(A2,LEN(A2)-FIND(",",A2,FIND(",",A2)+1))

some one might have shorter answer than that

Me transmitte sursum, Caledoni!

 

[Zathras]

Try the "Text to Columns" feature on the Data menu. Select both space and comma for delimiters.

Wherever you have more than one part to the city name it will flow over into 4 or even 5 columns. No worry -- sort on the last column to pull all of the cases together and then re-build the city name with something like
[tt]
=A1&" "&B1
( or =A1&" "&B1&" "&C1 where there are more than three parts)
[/tt]
and then copy / paste special / values to reconstruct the name.

 

[xlhelp]

As Jim Nabors would say, Golly..... I should have thought of that.


Me transmitte sursum, Caledoni!

 

[azoe]

Thanks to you both!! as people keep asking me - are you done yet?

 

[firefytr]

You can do this with all formula if you like. Such as for the zip code, just find the last space ...

=RIGHT(A8,LEN(A8)-FIND("~",SUBSTITUTE(A8," ","~",LEN(A8)-LEN(SUBSTITUTE(A8," ",""))),1))

You then have a length and starting place, then you can find the second to the last space for the state, etc.

Just an alternative.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci