Here is the overview of removing non-numeric characters in Excel.
How to Remove Non-numeric Characters from Cells in Excel: 2 Easy Ways
We have a dataset that contains various strings that combine numbers and other characters, and we’ll remove all non-numeric characters.
Method 1 – Nesting TEXTJOIN and INDIRECT Functions to Delete Non-numeric Characters from Cells in Excel
Steps:
- Use the following formula in cell C5.
=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:100")),1)+0,""))
- Press Enter.
➤ ROW(INDIRECT(“1:100”))
This function returns an array of {1,2,3,4,……..100}
➤ MID(B5,ROW(INDIRECT(“1:100”)),1)+0
This function returns {“4″;”0″;”0″;”6″;”J”;”a”;”c”;………..}
➤ IFERROR(MID(B5,ROW(INDIRECT(“1:100″)),1)+0,””)
This function returns {4;0;0;6;””;””;………..}
➤ TEXTJOIN(“”,TRUE,IFERROR(MID(B5,ROW(INDIRECT(“1:100″)),1)+0,””))
The TEXTJOIN function will join all those and return them as text. We are using TRUE to work with the values that remain.
- Drag the Fill Handle icon over the range of cells from C5 to cell C9.
Read More: How to Remove Numeric Characters from Cells in Excel
Method 2 – Applying VBA Code to Delete Non-numeric Characters from Cells
Steps:
- Press Alt+F11 on your keyboard. Excel will open the Visual Basic Editor.
- Click on Insert and select Module.
- Use the following code in the editor:
Function DeleteText(st As String)
Dim sR As String
sR = ""
For i = 1 To Len(st)
If True = IsNumeric(Mid(st, i, 1)) Then
sR = sR & Mid(st, i, 1)
End If
Next i
DeleteText = sR
End Function
- Save the VBA code and go back to your spreadsheet.
- Use the following formula in cell C5.
=DeleteText(B5)
- Press Enter.
- Drag the Fill Handle over the range of cells C6:C9.
- We have successfully deleted non-numeric characters using VBA code.
Read More: How to Remove Characters from String Using VBA in Excel
Download the Practice Workbook
Related Articles
- How to Remove Characters After a Specific Character in Excel
- Excel Remove Characters From Right
- How to Remove Characters from Left in Excel
- How to Remove Characters from Left and Right in Excel
- How to Remove Special Characters in Excel
<< Go Back To Excel Remove Characters | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Thank you that’s very helpful. I have tried using the subtitute function but it only works in some cases mostly not working until I found this tutorial. God Bless you.
Hello!
If in cells are for instance: ↑ 2,37226
How could that sign “↑” be removed? (preserving the numeric decimal data)
Regards,
José
Hi Jose,
You can choose any of the three methods given above to remove the “↑” sign. All of them work for any non-numeric character, including the one you are asking.
Hope that helped. If you are still facing problems, let us know.
FYI, the macro also deletes periods, which could be important if you need decimal places. I assume it would also do commas, so if those are the decimal marker that could be an issue as well.