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

 

[Sektor]

Data tab -> Text to columns. "Comma" as separator.

 

[ahs004]

Yes I have done that but the format of columns is not what i want.

<TABLE style="WIDTH: 738pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=985 x:str><COLGROUP><COL style="WIDTH: 340pt; mso-width-source: userset; mso-width-alt: 16566" width=453><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" span=3 width=143><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 340pt; HEIGHT: 18pt; BORDER-TOP: #ccccff 0.5pt solid; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl23 height=24 width=453>56 abc Close</TD><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; BACKGROUND-COLOR: white; WIDTH: 107pt; BORDER-TOP: #ccccff 0.5pt solid; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl22 width=143>Eltham</TD><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; BACKGROUND-COLOR: white; WIDTH: 107pt; BORDER-TOP: #ccccff 0.5pt solid; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl22 width=143>London</TD><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; BACKGROUND-COLOR: white; WIDTH: 107pt; BORDER-TOP: #ccccff 0.5pt solid; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl22 width=143>AB10 4AB</TD><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; BACKGROUND-COLOR: white; WIDTH: 77pt; BORDER-TOP: #ccccff 0.5pt solid; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl22 width=103> </TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 18pt; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl23 height=24>95 xyz Road</TD><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; BACKGROUND-COLOR: white; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl22>Downham</TD><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; BACKGROUND-COLOR: white; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl22>Bromley</TD><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; BACKGROUND-COLOR: white; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl22>Kent</TD><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; BACKGROUND-COLOR: white; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl22>BC1 4XY</TD></TR></TBODY></TABLE>

I wanted all postcodes and cities in same column

thanks

 

[Sektor]

Post some example.

 

[baitmaster]

welcome to the board

The following code should recreate this feature for you

Option Explicit
Sub addressToColumns()
Dim cl As Range
Dim arrAddress
Dim i As Integer

For Each cl In Selection
    
    arrAddress = Split(cl.Text, ",")
    
    Select Case UBound(arrAddress)
        Case 4
            For i = 0 To 4
                cl.Offset(0, i) = arrAddress(i)
            Next i
        Case 3
            cl = arrAddress(0)
            For i = 1 To 3
                cl.Offset(0, i + 1) = arrAddress(i)
            Next i
        Case Else
    End Select
Next cl
End Sub

this code works on whatever cells are selected when you start to run the macro, this can be changed if you want, tell me how. Ask if you don't know what to do with this code.

ALWAYS save a copy of your file before running untested code!

 

[ahs004]

Here is my original data.

As you can see some have 5 lines of address and some have 3 lines. All are seperated with commas.

I need to have the post codes in same columns and cities in another.

If i use Data tab -> Text to columns. "Comma" as separator, this puts them in different columns but it doesnt solve my problem.

Original Data

<TABLE style="WIDTH: 206pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=274 x:str><COLGROUP><COL style="WIDTH: 206pt; mso-width-source: userset; mso-width-alt: 10020" width=274><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 206pt; HEIGHT: 12.75pt; BORDER-TOP: #ccccff 0.5pt solid; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl24 height=17 width=274>318 abc Road,Downham,Bromley,Kent,BB1 4ZY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl24 height=17>22 Def Gardens,Eltham,London,SS9 4AB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl24 height=17>6 Ghi Close, Chichester, West Sussex, PP19 6TT</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl24 height=17>95 Jkl Road,Downham,Bromley,Kent,AR1 4AA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl25 height=17>38 Mno Road, London, W1 2BB</TD></TR></TBODY></TABLE>

Data after using -> Data tab -> Text to columns. "Comma" as separator.

<TABLE style="WIDTH: 266pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=355 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 74pt; HEIGHT: 12.75pt; BORDER-TOP: #ccccff 0.5pt solid; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl24 height=17 width=99>318 abc Road</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>Downham</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>Bromley</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>Kent</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>BB1 4ZY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl24 height=17>22 Def Gardens</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Eltham</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">London</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">SS9 4AB</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl24 height=17>6 Ghi Close</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"> Chichester</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"> West Sussex</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" colSpan=2> PP19 6TT</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #ccccff; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl24 height=17>95 Jkl Road</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Downham</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Bromley</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Kent</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">AR1 4AA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #ccccff 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #ccccff 0.5pt solid" class=xl25 height=17>38 Mno Road</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"> London</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"> W1 2BB</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR></TBODY></TABLE>

 

[bosco_yip]

A formula way :

1] Your list on the range of A1 to A5

2] B1, formula copy down :

=LEFT(A1,FIND(",",A1)-1)

3] C1, formula copy across to F1 and down :

=IF(LEN($A1)-LEN(SUBSTITUTE($A1,",",""))+COLUMN(B1)>5,TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",50)),COUNTIF($A1:B1,"*?")*50-49,50)),"")

Regards

 

[bosco_yip]

Or one formula, Cell B1 formula copy across to F1 and down :

=IF((COUNTIF($A1:A1,"*?")=1)+(LEN($A1)-LEN(SUBSTITUTE($A1,",",""))+COLUMN(A1)>5),TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",50)),COUNTIF($A1:A1,"*?")*50-49,50)),"")

Regards

 

[ahs004]

Hi BaitMaster,

I have used the below coding to split my addresses. because i have 30.000 addresses need splitting this is awfully slow process. i have left my pc running since last night but its hasnt finished.

Is there a faster way of doing this that you know of?

Many Thanks

Ahmet

welcome to the board

The following code should recreate this feature for you

Option Explicit
Sub addressToColumns()
Dim cl As Range
Dim arrAddress
Dim i As Integer
 
For Each cl In Selection
 
    arrAddress = Split(cl.Text, ",")
 
    Select Case UBound(arrAddress)
        Case 4
            For i = 0 To 4
                cl.Offset(0, i) = arrAddress(i)
            Next i
        Case 3
            cl = arrAddress(0)
            For i = 1 To 3
                cl.Offset(0, i + 1) = arrAddress(i)
            Next i
        Case Else
    End Select
Next cl
End Sub

this code works on whatever cells are selected when you start to run the macro, this can be changed if you want, tell me how. Ask if you don't know what to do with this code.

ALWAYS save a copy of your file before running untested code!

 

[baitmaster]

try switching off calculations and screen updates. Add a progress counter to the statusbar so you can monitor progress

Option Explicit
 
Sub addressToColumns()
Dim cl As Range
Dim arrAddress
Dim i As Integer
 
[COLOR=blue]Dim countTotal As Long: countTotal = Selection.Cells.Count
Dim countThis As Long[/COLOR]
[COLOR=blue][/COLOR] 
[COLOR=blue]With Application
    .ScreenUpdating = False
    .Calculation = -xlCalculationManual
    .StatusBar = Format(countThis / countTotal, "0.00%")
[/COLOR]    
    For Each cl In Selection
     
        arrAddress = Split(cl.Text, ",")
     
        Select Case UBound(arrAddress)
            Case 4
                For i = 0 To 4
                    cl.Offset(0, i) = arrAddress(i)
                Next i
            Case 3
                cl = arrAddress(0)
                For i = 1 To 3
                    cl.Offset(0, i + 1) = arrAddress(i)
                Next i
            Case Else
        End Select
    Next cl
    
[COLOR=blue]    .StatusBar = False
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
[/COLOR]End Sub