Seperate city, state, zip from 1 to 3 cells?

[caroutisine]

I have an export from an access database that puts the customer number, name, and address into an excel spreadsheet. The problem is that it puts the city, state and zip code into one cell for each customer. I need to sort by state and zip code, so I need to separate the city, state and zip code into three separate cells for each customer. There is no comma between the city and the state, only spaces.

There are a couple of issues that I'm having trouble with to sort this data:

1. The city may have one or more space between the name:
Example:
Fergus Falls MN 56537
or
South San Francisco CA 90025

2. Some of the Zip codes include the zip + 4
Example:
Louisville KY 40232-5070

Is there a way to tell Excel to take the 5 (or 5+4) numbers at the end of the cell entry and insert it into the next column? Then do the same thing with the state abbreviations? The state abbreviations are the only consistent thing in the cells, they are always between the city and the zip code, and are always two letters...can this somehow be used to tell Excel what to do?

Any help would be greatly appreciated.

 

[Brian from Maui]

Try,

Data/Text to Columns,

you may have to experiment. If you had a delimiter, it would be much easier

 

[SydneyGeek]

I took a formula approach because of the issues with Data | Text to Columns when you don't have a distinctive delimiter. Assume the data that you want to split is in A2. Create these formulas:

B2  =LEN(A2)
C2  =SEARCH(" ?? ",A2,1)
D2  =C2+3
E2  =LEFT(A2,C2-1)
F2  =MID(A2,C2+1,2)
G2  =MID(A2,D2+1,B2-D2)

Fill them down. They worked for me with the examples you gave.

These formulas work by sidestepping the issues of ZIP code length and the number of words in the City. Instead, Columns C and D find the spaces either side of the only constant -- the State abbreviation. Column B helps with the formula in Column G but you may want to incorporate in there instead.

HTH
Denis

 

[vmottert]

The Right and Left Text functions will work for you. But since both zip code forms are used you would have to use an If/Then to find out which form is used in each cell, then use that to implement the right function(s).

For example: = LEFT(RIGHT(C7,5),1) would return the "-" in the long zip coeds in cell C7.

Hope that helps.

 

[caroutisine]

Got it...

Thanks for your help.

 

[Tana Lee]

Aloha...

See the post:

FYI: Parsing (splitting) names and addresses (MSKb)

http://www.mrexcel.com/board2/viewtopic.php?t=59969&highlight=fyi+parsing

Tana-Lee