Split only the Names

[niladri20052006]

Hi All,

I have a list as follows

Long Beach 492,682
Los Angeles 3,792,621
Glendale 207,303
Santa Clarita 177,150
Yucca Valley
Torrance 149,111

I have 500+ data in this way. I want only the Text not the numbers like Long Beach , Los Angeles, Glendale...

Is there any better way to do that?

 

[Comfy]

=left(a1,min(find({"0","1","2","3","4","5","6","7","8","9"},a1&"0123456789"))-1)

 

[Sektor]

UDF.
Set reference: Tools -> References -> Microsoft VBScript Regular Expressions 5.5

Function ExtractText(Str As String) As String

    ' Set reference: Tools -> References -> Microsoft VBScript Regular Expressions 5.5
    
    Dim re As New RegExp
    Application.Volatile
    
    With re
        .Global = True
        .Pattern = "[\D]+"
    End With
    
    ExtractText = Trim(re.Execute(Str)(0))
    
End Function

 

[Weaver]

Or you could use late binding to avoid all the rigmarole with setting the reference

Function ExtractText(Str As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[\D]+"
        ExtractText = Trim(.Execute(Str)(0))
    End With
End Function

 

[niladri20052006]

Hi All,

The formula is absolutely awesome!!!

Thanks!