Split Address into columns

[ahs004]

I am trying to split addressed that i have into columns with using Text to Column option. However the addresses that i have is not consisted.

They are all comma seperated but some has address 1, address 2, town, city, postcode and others have address 1, town, city, postcode so the format i get is this;

address 1, address 2, town, city, postcode
address 1, town, city, postcode
address 1, town, city, postcode
address 1, address 2, town, city, postcode

is there a way where i can have it to align from right where at least the post code and cities be in the same column?

Thank you

 

[LadyLegacy]

So if I removed the commas, doing a control find replace with a space?

 

[baitmaster]

What exactly are you trying to do?

The thread is about trying to convert text strings into columns, using comma as a separator. But in your first example [Leary Bride Tinker & Moran 7 Ridgedale Avenue Cedar Knolls NJ 07927] you have no comma at all..? So how do you want to tell Excel how to split this?

Example 2 [CHAMBLEE, RYAN, KERSHAW & ANDERSON, P.C. 2777 NORTH STEMMONS FREEWA Dallas TX 75207] has commas in the company name but not in the address

And example 4 [Keais Records Service, Inc. 1010 Lamar, 18th floor Houston TX 77002] looks like it will partially work but is the only one that is structured anything like correctly. So the question is, how can you get the commas in to the other text strings that don't have them?

 

[LadyLegacy]

Unfortunately I see what you are saying. There is going to be no easy way for me to break out the name (payee), then the address and split the city, state and zip.

Of course since the data I receive is not uniformed it does create a very manual process. I was considering doing a mid formula, but since the names are all different lengths as well, I think my only chance is finding away to extract the names first, then go from there.

At least I know I wasn't trying to do this the hard way - there is only the hard way.

 

[baitmaster]

There's two parts to most Excel modelling

Data - time consuming and annoying. If this takes a long time then that's too bad
Calculations / Code / modelling - if this is taking you a long time, you're doing it wrong

 

[LadyLegacy]

I suppose I could do this in two parts, split the addresses that have commas to the ones that have spaces, so it is not mixed, then run the code you posted earlier, it worked for the most part, but those pesky inconsistencies just give me a headache.