How to split adres?

[Belleke]

Hello,
How can i split an adres
Ex:
In column A, I have Bridgestreet 5c
The result should be
In colum A :Bridgesheet
In colum B : 5
In colum C: c
Can somebody advice?
Thank you

 

[PCosta87]

Hi @Belleke,

You can use the following code, assuming addresses are in column A, starting in A1 (with no blank cells):

Sub SplitTextNum()
Dim r As Range, rC As Range
Dim v As Variant
Set r = Range("A1", Range("A1").End(xlDown))
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+|\D+)"
    .Global = True
    For Each rC In r
        v = Split(Mid(.Replace(rC.Value, "|$1"), 2), "|")
        rC.Offset(, 1).Resize(, UBound(v) + 1).Value = v
    Next rC
End With
End Sub

I shouldn't take any credit for this one though... just needed something similar long ago and found this online.

Still, hope it helps

 

[PCosta87]

Just for the fun of it... you can also use formulas to get the same result

See attached (Don't even ask )

 

[Belleke]

Thank you Pcosta
Works like a charm

 

[PCosta87]

Thank you Pcosta
Works like a charm

You are welcome