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

Recommended Productivity Software

Office Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).

arrow blue right bubble 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:


arrow blue right bubble 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.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

If you have installed Kutools for Excel, you can remove non-numeric characters as follows:

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.


Related articles:


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 More than 120 powerful advanced functions which designed for Excel:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase

Add comment


Security code
Refresh