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 With
End 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 With
End 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
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks using VBA:
VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: How to Count Cells with Specific Text
