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()
'Updateby Extendoffice
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:
3. Then all non-numeric characters in selected range are removed.
If the numbers with decimal points, you can use the following VBA:
Sub RemoveNotNum()
'Updateby Extendoffice
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 screenshot 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.
Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
After installing Kutools for Excel, please do as below:
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. And the non-numeric characters are removed from the selection immediately. See screenshot:
Click to Download Kutools for Excel and free trial Now!
For more detailed information about Remove Characters of Kutools for Excel, please visit Remove characters features description.
Demo: Remove numeric, alphabetic, non-printable or alphanumeric characters from cells
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
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!