How to Use VBA to Remove Numbers from a String in Excel – 4 Methods

This is an overview.

Below is the sample dataset.

Method 1 – Delete Leading Numbers from String with Excel VBA

Steps:

• Press Alt + F11 or go to Developer > Visual Basic to open Visual Basic Editor.

• In the Microsoft Visual Basic for Applications window, click Insert > Module.

• Enter the following code in the code window.

Public Function Delete_Lead_Num(val As String) As String
Dim l As Long
For l = 1 To Len(val)
Select Case Mid$(val, l, 1)
Case "0" To "9"
Case Else: Exit For
End Select
Next
Delete_Lead_Num = Mid$(val, l)
End Function

• Click Save to save the macro file as a User Defined Function (UDF).

• Go back to the worksheet and enter the user-defined function created in the VBA code and inside the brackets of the function, use the cell reference number: B5.
• The formula is:

• Press Enter.

The string is displayed without leading numbers.

• Drag down the Fill Handle to see the result in the rest of the cells.

Method 2 – Using VBA to Remove Trailing Numbers from a String

Steps:

• Open the  Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code in the code window.

Function Remove_Trail_Number(stdTxt As String)
Dim strg As String, x As Integer, y As Integer
stdTxt = Trim(stdTxt)
y = 1
For x = Len(stdTxt) To 1 Step -1
If IsNumeric(Mid(stdTxt, x, 1)) Then
strg = Left(stdTxt, Len(stdTxt) - y)
y = y + 1
Else: GoTo done
End If
Next x
done:
Remove_Trail_Number = Trim(strg)
End Function

• Press Ctrl + S to save the code.
• Enter the following formula and press Enter.

You will see the string without trailing numbers.

• Drag down the Fill Handle to see the result in the rest of the cells.

Method 3 – Extract All Numbers from a String Using VBA in Excel

Steps:

• Open the  Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code in the code window.

Function Remove_Number(Text As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
Remove_Number = .Replace(Text, "")
End With
End Function

• Click Save in the VBA Editor window.
• Go back to the worksheet and enter the user-defined function and use the cell reference number: B5 inside the brackets of the function.
• The formula is:

• Drag down the Fill Handle to see the result in the rest of the cells.

Method 4 – Separate Numbers Only from a String with Excel VBA

Consider the following dataset. To get the students’ Marks in the Data column:

Steps:

• Open the Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code in the code window.

Function Separate_Num(CellRf As String)
Dim StrgLength As Integer
StrgLength = Len(CellRf)
For x = 1 To StrgLength
If IsNumeric(Mid(CellRf, x, 1)) Then Result = Result & Mid(CellRf, x, 1)
Next x
Separate_Num = Result
End Function

• Press Ctrl + S to save the code.
• Enter the following and press Enter.
• Drag down the Fill Handle to see the result in the rest of the cells.

Download Practice Workbook

Download the free practice Excel template.

<< Go Back to Separate Numbers Text | Split | Learn Excel