Removing numbers from an address

[Lynne1]

I want to collect information on particular streets - my information includes the full address complete with the numbers of the residences in the particular streets - how can I remove the numbers?

 

[Sektor]

Post some example of address.

 

[Lynne1]

292 Patton Street
252 Argent Street
99 Brown Street
2 Ryan Lane

 

[Sektor]

UDF.

Function RemoveNumber(Str As String) As String

    Application.Volatile
    
    Dim re As Object
    
    Set re = CreateObject("VBScript.RegExp")
    
    With re
        .Global = True
        .Pattern = "\d"
    End With
    
    RemoveNumber = Trim$(re.Replace(Str, vbNullString))

End Function

 

[Lynne1]

Not sure of information.........I'm not that proficient?

Do you know of any formula I could use? I did have one, but for the life of me, I can't remember it!

 

[Sektor]

1. Press Alt+F11.
2. Go: Insert -> Module.
3. Paste code I wrote.
4. In cell type in (assuming address in cell A1): =RemoveNumbers(A1).

 

[Lynne1]

Wonderbar!!! Thank you so much..........works much better than I remember my formula did originally!!