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 string so easily.
Download Practice Template
You can download the free practice Excel template from here and practice on your own.
4 Methods to Remove Numbers from String using VBA
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.
1. Delete Leading Numbers from String with VBA Code
Steps to delete all the leading numbers from strings using VBA code are shown below,
- Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- In the pop-up code window, from the menu bar, click Insert -> Module.
- Copy the following code and paste it into the code window,
Public Function Remove_Lead_Num(value As String) As String
Dim i As Long
For i = 1 To Len(value) '//for loop each char
Select Case Mid$(value, i, 1) '//check current char
Case "0" To "9" '//allow chars
Case Else: Exit For '// i is the exit point
End Select
Next
Remove_Lead_Num = Mid$(value, i) '//remove lead num
End Function- This is not a Sub Procedure for the VBA program to run, this is creating a User Defined Function (UDF), which we will call in our worksheet to execute the task. So, after writing the code, instead of clicking the Run button, click on the Save button from the menu bar to save the macro file.
- 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).
- Press Enter.
You will get the string without all the leading numbers.
- 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. Remove Trailing Numbers from String by VBA Code
Steps to delete all the trailing numbers from strings using VBA code are given below,
- Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Function Remove_Trail_Num(stdText As String)
Dim str As String, i As Integer, b As Integer
'//removes the number from a longer text string
stdText = Trim(stdText)
b = 1
For i = Len(stdText) To 1 Step -1
If IsNumeric(Mid(stdText, i, 1)) Then
str = Left(stdText, Len(stdText) - b)
b = b + 1
Else: GoTo done
End If
Next i
done:
Remove_Trail_Num = Trim(str)
End Function
- Click Save.
- In the same way, go back to the worksheet of interest, write the user-defined function from the VBA code (Remove_Trail_Num, in the first line of the code), input the cell reference number of the cell of string that has trailing numbers (e.g. Cell B5) inside the brackets of the function and press Enter.
You will get the string without all the trailing numbers.
- 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. Strip All Numbers from String using VBA
Steps to delete all the leading numbers from strings using VBA code are shown below,
- Likewise, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
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.
- 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), input the cell reference number of the cell of string that has numbers (e.g. Cell B5) inside the brackets of the function and press Enter.
You will get the string without all of the numbers.
- 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 Numbers from String by VBA Code
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,
- Similarly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Function Separate_Num(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
Separate_Num = Result
End Function
- Click Save.
- Again, go back to the worksheet of interest, write the user-defined function from the VBA code (Separate_Num, in the first line of the code), inside the brackets of the function input the cell reference number of the cell of string that has numbers with it (e.g. Cell B5) and press Enter.
You will get the string without all the trailing numbers.
- Drag the cell down by Fill Handle to apply the formula to the rest of the cells to separate all the numbers from all the strings of the dataset.
Conclusion
In this article, we have learned how to remove numbers from various positions of string using VBA code in Excel. I hope that this article has been very beneficial to you. Feel free to ask any questions if you have regarding the topic.
