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:

HIGH ROAD NORTH 51

HIGH ROAD NORTH 51

WESTFIED ROAD 26

TYNE CLOSE 12

TYNE CLOSE 18

TYNE CLOSE 31

TYNE CLOSE 32

BANFORD CLOSE 1

BANFORD CLOSE 24

WILLIS AVENUE 3

WILLIS AVENUE 9

WILLIS AVENUE 15

WILLIS AVENUE 17

WILLIS AVENUE 24

WILLIS AVENUE 33

WILLIS AVENUE 34

<colgroup><col></colgroup><tbody>
</tbody>

...

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