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:
Recommended Productivity Tools
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:
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 functions, makes your jobs easier.|
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.
- 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
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 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...