How to Use VBA to Remove Numbers from String in Excel

While working with a large dataset, sometimes you may need to remove certain numbers from the data. To do this manually is really time-consuming and naive way. In Excel, you can implement VBA code to remove numbers from strings easily. In this article, we will explain how to use VBA to remove numbers from strings in Excel with 4 useful methods.

How to Use VBA to Remove Numbers from String in Excel: 4 Useful Methods

In this section, we will show you how to delete numbers from different positions of strings by implementing VBA code in Excel. Below is the dataset that we will be working with.

Now, let us go through the following methods to split numbers from strings.

1. Delete Leading Numbers from String with Excel VBA

Following are the steps to delete all the leading numbers from strings using VBA code.

Steps:

• First, press Alt + F11 on your keyboard or go to the tab Developer > Visual Basic to open Visual Basic Editor.

• Then, in the Microsoft Visual Basic for Applications window, from the menu bar, click Insert > Module.

• Afterward, insert the following code into 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

• After writing the code, instead of clicking the Run button, click on the Save button from the menu bar to save the macro file as this is a User Defined Function (UDF).

• Now, go back to the worksheet of interest and write the user-defined function that you just created in the VBA code (Remove_Lead_Num, in the first line of the code), and inside the brackets of the function, input the cell reference number of the cell of string that has leading numbers (e.g. Cell B5).
• Therefore, the formula will look like this.

• Then, press Enter.
• Finally, you will get the string without all the leading numbers.
• Lastly, drag the cell down by Fill Handle to apply the formula to the rest of the cells to delete all the leading numbers from all the strings of the dataset.

2. Use VBA to Remove Trailing Numbers from String

Steps to delete all the trailing numbers from strings using VBA code are given below.

Steps:

• First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window same as the first method.
• Then, insert this 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

• Afterward, press Ctrl + S to save the code.
• Now, input the following formula generated by the VBA code and press Enter.

• Finally, you will get the string without all the trailing numbers.
• Lastly, Drag the cell down by Fill Handle to apply the formula to the rest of the cells to delete all the trailing numbers from all the strings of the dataset.

3. Extract All Numbers from String Using VBA in Excel

Follow the steps below to delete all the leading numbers from strings using VBA code.

Steps:

• Likewise, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, type the following code.

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

• Next, click Save in the VBA Editor window.
• Again, go back to the worksheet of interest, write the user-defined function from the VBA code (Remove_Number, in the first line of the code), and input the cell reference number of the cell of string that has numbers (e.g. Cell B5) inside the brackets of the function.
• Therefore, the formula will look like this.

• Finally, press Enter and drag the cell down by Fill Handle to apply the formula to the rest of the cells to delete all the numbers from all the strings of the dataset.

4. Separate Only Numbers from String with Excel VBA

Suppose, you want to extract only the numbers from a large dataset of text and store it somewhere else. Consider the following dataset, from where we want to retrieve only the Marks of the students from the Data column.

Steps to separate numbers from strings using VBA code are shown below.

Steps:

• Similarly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, insert this code.

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

• Then, press Ctrl + S to save the code.
• Lastly, insert the following code > press Enter > apply Autofill.

Download Practice Workbook

You can download the free practice Excel template from here and practice on your own.

Conclusion

In this article, we have learned how to use VBA to remove numbers from string in Excel with 4 useful methods. I hope that this article has been very beneficial to you. Feel free to ask any questions, if you have regarding the topic.

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