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

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Remove non-numeric characters with VBA code

To remove the non-numeric characters from a range, please use this VBA as follows:

1. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and then copy and paste the following codes in 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 button to run the code, a dialog is appeared on the screen, and you can select a range to work with. See screenshot:

3. Then click OK, all the non-numeric characters in the current worksheet will been removed.

If the numbers are with decimal point, 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 shown as below:


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 Delete 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 Tools > Delete Characters…

doc-remove-non-numeric-characters3

2. In the Delete Characters dialog box, Check Non-numeric characters option from Delete Characters. You can instantly preview the results in the Preview pane. See screenshot:

3. Click OK or Apply, and the non-numeric characters are removed from the selection. See screenshots:

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


Related articles:


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

0#JW2013-12-24 02:15
sample code for comments
Reply | Reply with quote | Quote
0#jo2014-01-16 23:41
thankyou worked well
Reply | Reply with quote | Quote
0#Bryan Steven2014-03-11 15:17
wouldn't be better to replace the input-box method by just setting
WorkRng like this:
Set WorkRng = Intersect(ActiveSheet.UsedRange, Selection)
that way if user select a entire column it wouldn't generate any error.
Reply | Reply with quote | Quote
0#Julie Hodnett2014-03-26 03:15
Awesome Worked GREAT!!!!!!!!
Reply | Reply with quote | Quote

Add comment


Security code
Refresh