Macro to remove all the numbers from cells in a column

[tonywatsonhelp]

Hi Everyone,

I have data that come in written like this:

"T_Tester_Grey_600cm_66cm"
or
"Test_Bed_1600cm_266cm"

I want all the words but not the numbers so
"T_Tester_Grey"
"Test_Bed"

So I'd like a macro that can go down column C and either change all these or even better put the changed words into column Z

please help if you can
Tony

 

[KOKOSEK]

Do you really need VBA for it? It can be done by simple UDF (to put into cells in col. Z in your case)

Function FirstDigit(strData As String) As Integer
    Dim RE As Object, REMatches As Object
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .Pattern = "[0-9]"
    End With
    Set REMatches = RE.Execute(strData)
    FirstDigit = REMatches(0).FirstIndex
End Function

Book1
	A	B
1	T_Tester_Grey_600cm_66cm	T_Tester_Grey
2	Test_Bed_1600cm_266cm	Test_Bed
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=LEFT(A1,FirstDigit(A1)-1)

 

[tonywatsonhelp]

Hi Kokosec,
I think that will do nicely,
thank you very much
Tony

 

[KOKOSEK]

KOKOSEK, if You don't mind.
Happy to help.

 

[mohadin]

Hi
VBA option

Sub change()
Dim a As Variant
Dim i As Long
   a = Range("C1").Resize(Cells(Rows.Count, 3).End(xlUp).Row)
    With CreateObject("vbscript.regexp")
         .Pattern = "\_\d+.*"
            For i = 1 To UBound(a)
            a(i, 1) = .Replace(a(i, 1), "")
            Next
    End With
    Range("Z1").Resize(UBound(a)) = a
End Sub

 

[tonywatsonhelp]

Thank you Mohadin,
this is actually even better for what i'm trying to do,
Thanks very much
Tony

 

[mohadin]

You are welcome Tony
And thank you for the feedback
Be happy and safe