Splitting addresses into different cells

jawa912

New Member
Joined
Apr 13, 2016
Messages
15
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,566
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,176,616
Messages
5,904,037
Members
435,068
Latest member
StacyJ01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top