Sanjida Ahmed
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 so easily. In this article, we will explain how to use VBA to remove numbers from string in Excel with 4 useful methods.
Download Practice Workbook
You can download the free practice Excel template from here and practice on your own.
4 Useful Methods to Use VBA to Remove Numbers from String in Excel
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
Code Breakdown
- In the beginning, we created a User Defined Function (UDF) named Delete_Lead_Num.
- Then, we declared l=1 to Len (value) to calculate the number of characters in the text string with the LEN function.
- Afterward, we used the MID function to get a specific number of characters which can be between 0 to 9 and declared it as a Case.
- Lastly, the Delete_Lead_Num function will help to delete leading numbers returned from the Mid$(val, l, 1) argument.
- 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.
=Delete_Lead_Num(B5)
- 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.
Read More: How to Separate Numbers in Excel Using Formula (5 Ways)
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
Code Breakdown
- In the beginning, we created a User Defined Function (UDF) named Remove_Trail_Number.
- Then, we declared x=Len (stdTxt) to calculate the number of characters one after another with 1 Step -1 argument in the text string with the LEN function.
- Afterward, we used the MID function to get a specific number of characters and nested it with the ISNUMERIC function to return the value as a number.
- Here, y is declared as Integer which will be subtracted from the text with this argument Left(stdTxt, Len(stdTxt) from the string.
- Lastly, the Trim (strg) argument will split spaces in the Remove_Trail_Number function.
- Afterward, press Ctrl + S to save the code.
- Now, input the following formula generated by the VBA code and press Enter.
=Remove_Trail_Number(B5)- 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.
Read More: How to Extract Numbers after a Specific Text in Excel (2 Suitable Ways)
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
Code Breakdown
- In the beginning, we created a User Defined Function (UDF) named Remove_Number.
- Then, CreateObject(“VBScript.RegExp”) will help to return matches of regular expressions from string and replace them with RegEx matches.
- Afterward, we declared the Global property as TRUE to find all matches where the Pattern of numbers in the string will be in between 0 to 9.
- Otherwise, the Remove_Number function will be replaced by text with the Replace(Text, “”) argument.
- 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.
=Remove_Number(B5)- 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.
Read More: How to Extract Multiple Numbers from String in Excel (6 Methods)
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
Code Breakdown
- In the beginning, we created a User Defined Function (UDF) named Seperate_Num.
- Then, we declared StrgLength as an Integer where the number of characters is counted with the Len (CellRf) argument.
- Afterward, we used the IsNumeric(Mid(CellRf, x, 1)) argument to return a specific number of characters as numbers and then, applied the Result & Mid(CellRf, x, 1) to get the final output where StrgLength is x=1.
- Lastly, the Seperate_Num function will return the Result.
- Then, press Ctrl + S to save the code.
- Lastly, insert the following code > press Enter > apply Autofill.
=Separate_Num(B5)
Read More: How to Separate Numbers From Text in Excel VBA (3 Methods)
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. Follow ExcelDemy for more articles like this.
