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

 

[ahs004]

Hi Baitmaster,

I have tried this but it hasnt made a different. I had to end using task manager and only selected 100 lines to see if what the speed would be like but still very slow.

Do you think formula option will be faster and is there a formula that will do my job?

 

[ahs004]

Hi,

There must be a faster way of doing this. As i have total one 100K addresses to go through. this will take me weeks.

address are on 4 different tabs.

Many thanks

 

[baitmaster]

This isn't a perfect way to do this, but it sounds like its going slower than I would expect it to

Did you try Bosco_Yip's formula?
Do you have other files or memory-heavy processes going on?
Is a manual process going to be more efficient? Sometimes we over-complicate things in order to answer your question, when we might try something else just to get the result...

e.g. if I can't get an automated process, I would try a combination of auto and manual:

1. assign a reference # to every record: insert column > enter ref00001 against first record. Double click the fill handle (dot in bottom right corner of cell) to fill down all records
2. Data > text to columns, as you already tried
3. Data > sort, on last column, as this will group those records with 4 fields and those with 5
4. manually select and drag / insert column as required
5. Data > sort to re-order, using your reference #

 

[baitmaster]

I have just run my code on 50,000 lines of dummy data, it took under 30 seconds

I did notice that I forgot to increment my tracker value ("countThis") by 1 each time it went through a loop, so it always reports 0% progress... hope that hasn't misled you...

With Application
    .ScreenUpdating = False
    .Calculation = -xlCalculationManual
    
    For Each cl In Selection
    
[COLOR=blue]        countThis = countThis + 1
        .StatusBar = Format(countThis / countTotal, "0.00%")
[/COLOR]     
        arrAddress = Split(cl.Text, ",")

 

[ahs004]

Hi Baithmaster,

That is perfect, i was being stupid there. My file has alot formulas and count macros so i copied my tab to another fresh sheet and ran my macro.

I noticed i have 1 small problem now. Formula hasnt worked on some addresses;
here is a sample address

12 St Sample's Close,Magazine Road,test,sheet,TZ45 8PY

Could id be because '?

Many thanks

Ahmet

 

[Phobe]

I have the exact same problem as the OP and while baitmaster's solution does exactly what I want (thank you very much) it still leaves about 10% of my data untouched - ie running the macro on those has no effect.

It seems that the macro fails when there's a single number followed by a comma in the beginning of the address, for example:

4, Street name, county, XX1 YY2

will cause the macro to fail,
while
4 Street name, county, XX1 YY2

will work correctly.

If anyone has a solution for this it would be greatly appreciated.

 

[Phobe]

update - actually what seems to be causing the issue is that the number of commas can vary from 2 to 5 or 6.
Too many or too little commas in an adress line will cause the formula to fail - deleting or adding commas will make it work ok.

Any help?

 

[baitmaster]

To solve this, you need to understand what the code provided is actually doing and why. You can then tailor this to meet your own needs, which vary slightly from the OP

The code [arrAddress = Split(cl.Text, ",")] is taking an individual address, and splitting into an array of text strings, by effectively replacing comma with a split. Think Data > Text to Columns function, with the results stored in memory (in an Array) rather than written to cells

example:
"1, street, town, post code"
becomes
[1]
[street]
[town]
[post code]
This array contains 4 elements. The function UBOUND(array) will return 3, because the array contains items 0, 1, 2 and 3 (arrays start at 0 unless you define otherwise)

I hard-coded exactly what to do when UBOUND returns either 3, or 4 (5 elements in array). It will not do anything if your address contains a different number of commas, but you can add additional sections into the SELECT CASE section that perform different actions

hope this helps

 

[LadyLegacy]

This is ALMOST what I am looking for, my problem is my addresses have commas splitting the names, here is a sample of the differences between the addresses:

[TABLE="width: 700"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Leary Bride Tinker & Moran 7 Ridgedale Avenue Cedar Knolls NJ 07927[/TD]
[/TR]
[TR]
[TD]CHAMBLEE, RYAN, KERSHAW & ANDERSON, P.C. 2777 NORTH STEMMONS FREEWA Dallas TX 75207[/TD]
[/TR]
[TR]
[TD]Esquire Deposition Solutions LLC P O Box 846099 Dallas TX 75284[/TD]
[/TR]
[TR]
[TD]Keais Records Service, Inc. 1010 Lamar, 18th floor Houston TX 77002[/TD]
[/TR]
[TR]
[TD]America First 325 N St. Paul St. Dallas TX 75201[/TD]
[/TR]
[TR]
[TD]Colin J. Duffey, Associates 15 Casper Berger Road Whitehouse Station NJ 08889[/TD]
[/TR]
</tbody>[/TABLE]


ANY help is GREATLY appreciated!!

 

[baitmaster]

Hi, your problem is that you have a mix of comma use, and no way to easily tell Excel which is which. I note some company names include commas whereas some addresses don't. You need to find a way to standardise your data before your code will be able to process it. Your problem then is that you probably want your code to do the standardising, so you're stuck in a catch 22. I can't currently see you automating this task, it requires too much human decision making