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


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:

 

Recommended Productivity Tools

Office Tab

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

Kutools for Excel

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

gold star1200 New Features for Excel, Make Excel Much Easy and Powerful:

  • 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

Comments  

Permalink 0 JW
sample code for comments
2013-12-24 02:15 Reply Reply with quote Quote
Permalink 0 jo
thankyou worked well
2014-01-16 23:41 Reply Reply with quote Quote
Permalink 0 Bryan Steven
wouldn't be better to replace the input-box method by just setting
WorkRng like this:
Set WorkRng = Intersect(Activ eSheet.UsedRang e, Selection)
that way if user select a entire column it wouldn't generate any error.
2014-03-11 15:17 Reply Reply with quote Quote
Permalink +1 Julie Hodnett
Awesome Worked GREAT!!!!!!!!
2014-03-26 03:15 Reply Reply with quote Quote
Permalink 0 Daniel
Wow. that worked , exactly what I needed.

Thanks,
2014-07-25 20:12 Reply Reply with quote Quote
Permalink 0 amanda
I received an error when I hit the > run button in the developer:

Compile Error:
Expected:end of statement


and the word "non" in non-numeric is highlighted
2014-11-21 13:37 Reply Reply with quote Quote
Permalink 0 Easter2015
Use this Formaula for replacing the existing character with the new one
=SUBSTITUTE(text,old_text,new_text,[instance_num])
for ex-=SUBSTITUTE(TRIM(G1),"/","")
Wish U all Happy Easter 2015
2015-03-19 17:16 Reply Reply with quote Quote
Permalink 0 Roger
Thanx Guys for such a nice work. The script is awesome and it worked for. Keep it up guys.
2015-06-08 16:25 Reply Reply with quote Quote
Permalink 0 Cole
Just wanted to let you know that if the string starts with zero(s), or starts with letters, followed by zero(s), followed by the rest of the string this will remove all of the beginning zeroes. This is weird because it doesn't remove the zero(s) if they are between other non-zero numbers within the string, only if they start the string or are the first numbers after the initial letters in a string.

Example.

0060100 would come out as 60100

PFF057726 would come out as 57726.

Let me know if you have an explanation for this and can think of a solution. Thank you.
2015-09-24 18:44 Reply Reply with quote Quote
Permalink 0 Naresh kumar
Send me it on my email id
2016-12-21 09:03 Reply Reply with quote Quote
Permalink 0 samola
Hi,

I want to use this macro but want to pre-define a range. Can you please let me know how to pre-define the range.

Thanks,
Samit
2017-03-07 21:27 Reply Reply with quote Quote

Add comment


Security code
Refresh