Splitting text into columns after the last comma

[FooeyLui]

Hi

I have address data that has different amounts of commas in each address, like this:
[TABLE="width: 743"]
<col><tbody>[TR]
[TD]company, street, city, bn1 3ry[/TD]
[/TR]
[TR]
[TD]street, city, bn2 4jy[/TD]
[/TR]
[TR]
[TD]street, city, bn23yt[/TD]
[/TR]
[TR]
[TD]street, city, wc10 7yt[/TD]
[/TR]
</tbody>[/TABLE]

If I split text to columns the fields don't end up in the same columns:
[TABLE="width: 471"]
<col><col span="3"><tbody>[TR]
[TD]company[/TD]
[TD] street[/TD]
[TD] city[/TD]
[TD] bn1 3ry[/TD]
[/TR]
[TR]
[TD]street[/TD]
[TD] city[/TD]
[TD] bn2 4jy[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]street[/TD]
[TD] city[/TD]
[TD] bn23yt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]street[/TD]
[TD] city[/TD]
[TD] wc10 7yt[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want all the postcodes, city, street info to go in the same column. like this:
[TABLE="width: 471"]
<col><col span="3"><tbody>[TR]
[TD]company[/TD]
[TD]street[/TD]
[TD] city[/TD]
[TD] bn1 3ry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]street[/TD]
[TD] city[/TD]
[TD] bn2 4jy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]street[/TD]
[TD] city[/TD]
[TD] bn23yt[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]street[/TD]
[TD] city[/TD]
[TD] wc10 7yt[/TD]
[/TR]
</tbody>[/TABLE]

I tried using the RIGHT function eg =RIGHT(A1,7). Problem is that the postcodes are all different lengths, so I can't use that either.

Please can someone let me know how I split this data? Thanks

 

[MickG]

Depending on the uniformity of you addresses , this might work.

[COLOR="Navy"]Sub[/COLOR] MG21Oct54
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Tc [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    oMax = Application.Max(oMax, UBound(Split(Dn, ",")))
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dn = vbNullString [COLOR="Navy"]Then[/COLOR]
        Tc = Split(Dn, ",")
        Dn.Offset(, oMax - UBound(Tc) + 1).Resize(, UBound(Tc) + 1) = Tc
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[FooeyLui]

Hi

Sorry, I don't know how to use this. I've never used visual basic before. I tried opening visual basic and pasting it in, but that didn't work. I'm only used to using formulas.

Please can you let me know how I run this or if there is a formula which is the same as this?

Many thanks

 

[bschwartz]

If you just want the Zip Code try:

=RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1,",","^",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1)

 

[MickG]

To Save and Run code:-
Copy code from Thread
In Data sheet , Click "Alt+F11",:- Vb Window appears.
From the Vb window toolbar, Click "Insert" , " Module":- New Vb window appears .
Paste Code into this window.
Close VB Window
On active sheet select "Developer" tab.
From Ribbopn , select "Macro":- Macro dialog box Appears.
Select Code Name from list.
On right of Dialog Box , Select "Run".
The sheet should now be Updated.
Regrds Mick

 

[FooeyLui]

Hi

This works brilliantly. Thanks. I've been trying to work out how it works but I don't understand it! I find that address data is generally quite uniform at the end, so splitting data from the right works better than from the left. I was hoping that I could adapt this formula to then take out the city, and the next data, and the next - always looking from the right comma by comma. Please can you let me know how I change it to do this? Many thanks