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

 

[Joe4]

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.

Very true! Excel cannot think for itself (at least not yet). It is very literal, and can only do what we tell it to.

The advice that I give people on these type of issues is to spell out all the rules it should follow.
If you are unable to spell out all the rules (in plain English) that cover all the bases, then you are probably are not going to be able to program it.