How to Use VBA to Remove Numbers from String in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

VBA code to Remove Numbers from String


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.

Dataset to Remove Numbers from String


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.

Choosing Visual Basic from Developer Tab

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

Selecting Module from Insert Tab to Insert VBA

  • 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

VBA code to Remove Numbers from String

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).

Saving VBA Code

  • 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)

Applying Formula to Delete Leading Numbers from String in Excel

  • 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.

Final Output on Using VBA to Delete Leading Numbers from String in Excel


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

Inserting VBA Code to Remove Trailing Numbers from String

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.

Output of Applying VBA to Remove Trailing Numbers


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

Using VBA to Extract All Numbers from String in Excel

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.

Result of Using VBA to Extract All Numbers from String in Excel


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.

Dataset for Separating Only Numbers

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

Applying VBA to Separate Only Numbers from String

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)

Final output of Applying VBA to Separate Only Numbers from String


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo