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
Code Breakdown
A User Defined Function (UDF): Delete_Lead_Num is created. l=1 is declared to Len (value) to calculate the number of characters in the text string with the LEN function. The MID function gets a specific number of characters between 0 to 9 and is declared as Case. The Delete_Lead_Num function deletes the leading numbers returned by the Mid$(val, l, 1) argument.- 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:
=Delete_Lead_Num(B5)
- 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
Code Breakdown
A User Defined Function (UDF): Remove_Trail_Number is created. x=Len (stdTxt) is declared to calculate the number of characters with the 1 Step -1 argument in the LEN function. The MID function is used to get a specific number of characters and is nested with the ISNUMERIC function to return the value as a number. y is declared as Integer and is subtracted from the text with the argument Left(stdTxt, Len(stdTxt). The Trim (strg) argument splits spaces in the Remove_Trail_Number function.- Press Ctrl + S to save the code.
- Enter the following formula and press Enter.
=Remove_Trail_Number(B5)
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
Code Breakdown
A User Defined Function (UDF): Remove_Number is created. CreateObject(“VBScript.RegExp”) returns matches of regular expressions and replaces them with RegEx. Global is declared as TRUE to find all matches, in which the Pattern of numbers is between 0 to 9. Otherwise, the Remove_Number function will be replaced with text using the Replace(Text, “”) argument.- 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:
=Remove_Number(B5)
- 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
Code Breakdown
A User Defined Function (UDF): Seperate_Num is created. StrgLength is declared as Integer and the number of characters is counted with the Len (CellRf) argument. The IsNumeric(Mid(CellRf, x, 1)) argument returns a specific number of characters as numbers and the Result & Mid(CellRf, x, 1) gets the final output: StrgLength is x=1. The Seperate_Num function returns the Result.- 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.
=Separate_Num(B5)
Download Practice Workbook
Download the free practice Excel template.
<< Go Back to Separate Numbers Text | Split | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!