Splitting addresses into different cells

[jawa912]

I need to separate addresses that are in one cell into address,city,state,zip cells. There isn't a consistent deliminator and I can only get ad far as pulling out the state and zip. Below is a sample of the info that I need to be separated. I think I've sprouted my first grey hair from this endeavor :/

18911 NW 10TH ST PEMBROKE PINES,FL 3302915339 NW 7TH ST PEMBROKE PINES,FL 33028-1841
401 OLD DIXIE HWY STE 3655 JUPITER,FL 33469
159 MARTIN CIRCLE ROYAL PALM BEACH,FL 33411
2345 NE 4TH AVE BOCA RATON,FL 33431
9940 GARDENS E DR PALM BEACH GARDENS,FL 33410
11311 NMW 27 ST SUNRISE,FL 33323
18466 49TH ST N LOXAHATCHEE,FL 33470-2366
1057 SUMMITT TRAIL CIR APT B WEST PALM BEACH,FL 33415
730 NW 57 PL FORT LAUDERDALE,FL 33309
15775 133RD TERRACE NORTH JUPITER,FL 33478-0000
4908 PALO VERDE DR BOYNTON BEACH,FL 33436
8129 RODEO DR LAKE WORTH,FL 33467
13045 52ND CT N WEST PALM BEACH,FL 33411
2721 VISTA PKWY STE 10 WEST PALM BEACH,FL 33411-2731
12861 58TH PL N ROYAL PALM BEACH,FL 33411-8553
PO BOX 3155 LANTANA,FL 33467
4892 DELL AVE APT 1 LAKE WORTH,FL 33461

 

[JLGWhiz]

If you can go through the list and make sure that there is only one address per line and place a comma at the end of the street address, then the following code will do the rest. This assumes all data is in column A beginning in row 1 with no headers.

Sub t()
Dim c As Range, spl As Variant
    For Each c In Range("A1", Cells(Rows.Count, 1).End(xlUp))
        spl = Split(c.Value, ",")
        c = spl(0)
        c.Offset(, 1) = spl(1)
        c.Offset(, 2) = Left(spl(2), 2)
        c.Offset(, 3) = Mid(spl(2), 3)
    Next
Columns("A:D").Autofit
End Sub

 

[jawa912]

Unfortunately, this isn't an option. There are over 15,000 lines on this sheet. I currently have the address column as "B" but that's easy enough to move to A

 

[JLGWhiz]

I don't think you will solve it any other way, but I have been wrong before.
Regards, JLG

 

[pvr928]

Hi jawa912

There are definite patterns in the data which mean it is likely it can be done. You're likely to have to 'process' the data a couple of times, splitting it out into its component parts, until you arrive a final data set.

Separating out the city looks like it will be the hardest, as it appears the city name could be any of 1 to 3 words long. But given some rules, it can be done. If you had a library of city names that might help (but still could give rise to difficulties if there are minor differences in format).

That said, the resultant VBA would be fairly complex, so I'm not willing to commit to attempting it. It is likely not to be 100% accurate without human intervention to review the data set, but you would get pretty close.

Cheers

pvr928

 

[Kyle123]

You'd be better off using a paid online service. There are loads out there that specialise in cleaning up this sort of mess, they're reasonably priced and will likely cost you a lot less than the time required to fix this.

 

[István Hirsch]

You should copy the Street Suffix Abbreviations into your Workbook (Sheet2 at me, from $A$5 to $C$506). Then try this to extract the address:

Excel Workbook
	A	B
1	401 OLD DIXIE HWY STE 3655 JUPITER,FL 33469	401 OLD DIXIE HWY
2	159 MARTIN CIRCLE ROYAL PALM BEACH,FL 33411	159 MARTIN CIRCLE
3	2345 NE 4TH AVE BOCA RATON,FL 33431	2345 NE 4TH AVE
4	9940 GARDENS E DR PALM BEACH GARDENS,FL 33410	9940 GARDENS
5	11311 NMW 27 ST SUNRISE,FL 33323	11311 NMW 27 ST
6	18466 49TH ST N LOXAHATCHEE,FL 33470-2366	18466 49TH ST
7	1057 SUMMITT TRAIL CIR APT B WEST PALM BEACH,FL 33415	1057 SUMMITT TRAIL
8	730 NW 57 PL FORT LAUDERDALE,FL 33309	730 NW 57 PL
9	15775 133RD TERRACE NORTH JUPITER,FL 33478-0000	15775 133RD TERRACE
10	4908 PALO VERDE DR BOYNTON BEACH,FL 33436	4908 PALO VERDE DR
11	8129 RODEO DR LAKE WORTH,FL 33467	8129 RODEO DR
12	13045 52ND CT N WEST PALM BEACH,FL 33411	13045 52ND CT
13	2721 VISTA PKWY STE 10 WEST PALM BEACH,FL 33411-2731	2721 VISTA
14	12861 58TH PL N ROYAL PALM BEACH,FL 33411-8553	12861 58TH PL
15	PO BOX 3155 LANTANA,FL 33467	#VALUE!
16	4892 DELL AVE APT 1 LAKE WORTH,FL 33461	4892 DELL AVE
Sheet1

 

[Joe4]

Because of the variance of the data (the lack of "hard and fast" rules), I think you would be hard-pressed to come up with any solution that is guaranteed to be 100% every time.
Not unless it was a paid service tied in to some database of street names and zip codes.

 

[István Hirsch]

Are all addresses for Florida?

 

[JLGWhiz]

This shows that computers cannot think like humans. We can look at the data and know where it needs to be separated in 99.9 percent of the entries. But the computer has to be told where and how to separate the data based on something it can detect. That something is missing from the sample data.