Address parsing formula

[StevenPS]

Members:
I have addresses that we extract from an old mainframe system into Excel. The addresses are not parsed into City, State Zip. This data comes over in one cell.

So one cell can have:

W City IL 45678

the next cell can have
Town AZ 78945-1235

The next can be:
East Washington NY 23456 6547

What we want is to get the city in one column, the state in the next column and the zip (plus four when present) in the last column.

The text to columns does not work well because of the variations in the spacing in compound city names plus the zip and zip plus 4.


Thanks in advance
Steve

 

[Scott Huish]

Here you go:

In
B1 =TRIM(SUBSTITUTE(A1,C1&" "&D1,""))
C1 =LEFT(RIGHT(A1,LEN(D1)+3),2)
D1 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

Copy down.

Excel 2010
	A	B	C	D
1	W City IL 45678	W City	IL	45678
2	Town AZ 78945-1235	Town	AZ	78945-1235
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(SUBSTITUTE(A1,C1&" "&D1,""))
B2	=TRIM(SUBSTITUTE(A2,C2&" "&D2,""))
C1	=LEFT(RIGHT(A1,LEN(D1)+3),2)
C2	=LEFT(RIGHT(A2,LEN(D2)+3),2)
D1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
D2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))

 

[Scott Huish]

Oops, I just saw that your last zip+4 sample doesn't contain a hyphen. Is that how your data actually is?

 

[Rick Rothstein]

Assuming your data starts in cell A1, put these formulas in the indicated cells and then copy them down to the end of your data...

B1: =LEFT(A1,LEN(IF(OR(MID(A1,LEN(A1)-4,1)={" ","-"}),LEFT(A1,LEN(A1)-5),A1))-9)

C1: =MID(A1,LEN(B1)+2,2)

D1: =SUBSTITUTE(MID(A1,LEN(B1)+LEN(C1)+3,10)," ","-")

 

[StevenPS]

Thanks Guys,
It looks like I should be able to make these formulas work. this old main frame data base has a lot of ugly addresses with some strange formatting. We will still have a bit of manual clean up. This should help with 99% of the data.

Steve

 

[Rick Rothstein]

Thanks Guys,
It looks like I should be able to make these formulas work. this old main frame data base has a lot of ugly addresses with some strange formatting. We will still have a bit of manual clean up. This should help with 99% of the data.

Can you show us some examples of addresses that the formulas I posted won't be able to handle? I'm thinking, perhaps, that I might be able to modify the formulas to account for how they differ from the examples you have already shown us.

 

[bladz1454]

Can you show us some examples of addresses that the formulas I posted won't be able to handle? I'm thinking, perhaps, that I might be able to modify the formulas to account for how they differ from the examples you have already shown us.

Sorry for spark an old thread (these have worked awesome).

Any chance you can strip down addresses in this format:

1201 Fun Avenue Hudson, FL 34669

I am able to pull the State and Zip but the rest keeps coming out in one piece:

1201 Fun Avenue Hudson

When I would ideally like:

1201 Fun Avenue
Hudson

What makes this even more difficult is the addresses are not consistent... some have commas like this:

114 Fairburn Ave. Clearwater, FL 33755

Thanks!