While working with Microsoft Excel, we may find data combined with text and numeric numbers. Sometimes, you need to know the numeric value of that particular cell. In order to do that, you have to remove the non-numeric characters from the cell. In this tutorial, we are going to show you how to remove non-numeric characters from cells in Excel. Here is our overview image of removing non-numeric characters in Excel.
How to Remove Non-numeric Characters from Cells in Excel: 2 Easy Ways
We are solving this problem in 2 ways. The first one is using the formulas, and the second one is using the VBA codes. We recommend you learn and try all these methods. It will upgrade your Excel knowledge.
To demonstrate this tutorial, we are going to use this dataset.
Method 1: Nesting TEXTJOIN and INDIRECT Functions to Delete Non-numeric Characters from Cells in Excel
Generally speaking, the formulas we are giving you are almost the same. Our main function will be the TEXTJOIN function. Here, we are implementing the TEXTJOIN function along with the INDIRECT function. We hope you will learn and apply these all to your
Steps:
- Firstly, type the following formula in cell C5.
=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:100")),1)+0,""))
- Then, 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,””))
Finally, the TEXTJOIN function will join all those and return them as text. We are using TRUE to work with the values that remain.
- After that, drag the Fill Handle icon over the range of cells from C5 to cell C9.
- Therefore, we are successful in removing non-numeric characters from the cells.
Read More: How to Remove Numeric Characters from Cells in Excel
Method 2: Applying VBA Code to Delete Non-numeric Characters from Cells
Now, if you know Excel’s VBA then you should try this method. This method is not only efficient but also will save you a lot of time. If you find the previous formulas slightly difficult, this method definitely will be your go-to method to solve this problem.
Steps:
- Firstly, press Alt+F11 on your keyboard. After that, it will open the Visual Basic Editor.
- Secondly, click on Insert > Module.
- Thirdly, type 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
- Then, save the VBA code and go back to your spreadsheet.
- So, type the following formula in cell C5.
- After that, type the following formula in cell C5.
=DeleteText(B5)
- Then, press Enter.
- Therefore, drag the Fill Handle over the range of cells C6:C9.
- Finally, you can see, that we have successfully deleted the non-numeric characters using the VBA code.
Read More: How to Remove Characters from String Using VBA in Excel
Download Practice Workbook
Please download this practice workbook
Conclusion
To conclude, we hope this tutorial will help you to remove non-numeric characters from cells in Excel. We highly recommend you download the practice workbook and try it yourself. Also, feel free to give your valuable feedback in the comment section. Your valuable feedback keeps us motivated and creates a lot of articles like this.
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.