How to remove non numeric characters from cells in Excel?

Supposing you have a worksheet with lots of such data ABCD4578124YUIOH, and you only want to remove the non-numeric characters (ABCDYUIOH) but keep the numeric characters in cells. Of course, you can remove those characters one by one, but right here you can get rid of the non-numeric characters from cells quickly as follows:

Remove non-numeric characters with VBA code

A handy tool to remove non-numeric characters with one click

Remove non-numeric characters with VBA code

To remove non-numeric characters from a range with VBA code, please do as follow:

1. Click Developer > Visual Basic to open the Microsoft Visual Basic for applications window. In the Microsoft Visual Basic for applications window, click Insert > Module, and then copy and paste the following codes into the module:

VBA: Remove all non-numeric characters

Sub RemoveNotNum()
'Updateby20131129
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xOut = ""
    For i = 1 To Len(Rng.Value)
        xTemp = Mid(Rng.Value, i, 1)
        If xTemp Like "[0-9]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub

2. Then click the button to run the code, In the popping up KutoolsforExcel dialog box, select a range with the non-numeric characters you want to remove, and then click the OK button. See screenshot:

Then all non-numeric characters in selected range are removed.

If the numbers with decimal points, you can use the following VBA:

Sub RemoveNotNum()
'Updateby20131129
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xOut = ""
    For i = 1 To Len(Rng.Value)
        xTemp = Mid(Rng.Value, i, 1)
        If xTemp Like "[0-9.]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub

You can see the results as below screenshots shown:

A handy tool to remove non-numeric characters with one click

For removing non-numeric characters in a range, Kutools for Excel’s Remove Characters utility can get it done with one click.

After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)

1. Highlight the range you want to remove non-numeric characters. Click Kutools > Text > Remove Characters.

2. In the Remove Characters dialog box, check the Non-numeric option in the Remove Characters section, and then click the OK or Apply button. You can instantly preview the results in the Preview pane. See screenshot:

3. Now the non-numeric characters are removed from the selection immediately. See screenshots:

For more detailed information about Remove Characters of Kutools for Excel, please visit Remove characters features description.

  Demo

Related articles:

• Remove alpha characters from cells
• Remove numeric characters from cells
• Remove non-printable characters from cells
• Remove non-alpha characters from cells
• Remove non-alphanumeric characters

Recommended Productivity Tools

Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

Add comment