Text to Columns/Formula to Separate Address Data

[m_geragh]

I have tried many different ways to separate the house number from the street name:
=(right) function with 'find' number
text to columns
replacing 'street' with 'address,' etc so I could separate by the ',' but there is so many variables.

Does anybody have an easy way to separate the house number from the street name in the below fictinary sample:

[TABLE="width: 195"]
<colgroup><col></colgroup><tbody>[TR]
[TD]HIGH ROAD NORTH 51
[/TD]
[/TR]
[TR]
[TD]HIGH ROAD NORTH 51
[/TD]
[/TR]
[TR]
[TD]WESTFIED ROAD 26
[/TD]
[/TR]
[TR]
[TD]TYNE CLOSE 12
[/TD]
[/TR]
[TR]
[TD]TYNE CLOSE 18
[/TD]
[/TR]
[TR]
[TD]TYNE CLOSE 31
[/TD]
[/TR]
[TR]
[TD]TYNE CLOSE 32
[/TD]
[/TR]
[TR]
[TD]BANFORD CLOSE 1
[/TD]
[/TR]
[TR]
[TD]BANFORD CLOSE 24
[/TD]
[/TR]
[TR]
[TD]WILLIS AVENUE 3
[/TD]
[/TR]
[TR]
[TD]WILLIS AVENUE 9
[/TD]
[/TR]
[TR]
[TD]WILLIS AVENUE 15
[/TD]
[/TR]
[TR]
[TD]WILLIS AVENUE 17
[/TD]
[/TR]
[TR]
[TD]WILLIS AVENUE 24
[/TD]
[/TR]
[TR]
[TD]WILLIS AVENUE 33
[/TD]
[/TR]
[TR]
[TD]WILLIS AVENUE 34
[/TD]
[/TR]
</tbody>[/TABLE]
...

Many Thanks in advance

 

[jimrward]

i used google to find the following assuming your data is in column A and starts in A2

=IF(ISERR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

 

[XOR LX]

Or, since in your examples the Number always forms the final part of the string, in B2:

=--TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",187)),187))

And in C2:

=TRIM(SUBSTITUTE(A2,B2,""))

Regards

 

[m_geragh]

Thank you this gives me the house number, which is at least a good starting point

 

[m_geragh]

Or, since in your examples the Number always forms the final part of the string, in B2:

=--TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",187)),187))

And in C2:

=TRIM(SUBSTITUTE(A2,B2,""))

Regards

Absolutely spot on - thank you!

 

[XOR LX]

You're welcome!

 

[apo]

Heres another.. more typing than the formula and slower.. but in any case..

Private Sub CommandButton1_Click()
   Dim objRegex, n
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "\d*\d$"
        For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
            Set myMatches = .Execute(Cells(i, 1))
            For Each n In myMatches
                    Cells(i, 2).Value = n
            Next n
        Next i
    End With
End Sub

 

[jimrward]

If you are going to use vba you might as well use the split function on the string and return the Nth element