Table of Contents
Removing numbers from a string using VBA can be accomplished by using the VBA function “Replace”. This function allows for the replacement of specific characters or strings within a given string. By specifying the numbers to be removed as the “OldText” and leaving the “NewText” blank, the function can effectively remove all instances of the specified numbers from the string. This can be useful in various situations, such as cleaning up data or formatting strings for display purposes. By utilizing this function, the process of removing numbers from a string using VBA can be easily and efficiently achieved.
VBA: Remove Numbers from String
You can create the following function in VBA to remove numbers from a string in a cell in Excel:
Function RemoveNumbers(CellText As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers = .Replace(CellText, "")
End WithEnd Function
You can then use this function to remove the numbers from any cell you’d like in Excel.
The following example shows how to use this function in practice.
Example: Remove Numbers from String Using VBA
Suppose we have the following list employee ID’s in Excel:
Suppose we would like to remove the numbers from each string in the Employee ID column.
We can create the following function in VBA to do so:
Function RemoveNumbers(CellText As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers = .Replace(CellText, "")
End WithEnd Function
Once we’ve created this function, we can then type the following formula into cell B2 to return the employee ID in cell A2 with all numbers removed:
=RemoveNumbers(A2)
We can then click and drag this formula down to each remaining cell in column B:
Column B displays each of the strings in column A with the numbers removed.
For example:
- 4009Andy becomes Andy
- 1540Bob becomes Bob
- 1500Chad09 becomes Chad
- 1600Doug becomes Doug
Additional Resources
The following tutorials explain how to perform other common tasks using VBA: