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.
Related Articles
Download Practice Workbook
Please download this practice workbook
2 Ways to Remove Non-Numeric Characters from Cells in Excel
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:
1. Using Formulas 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 and SEQUENCE functions. We hope you will learn and apply these all to your dataset.
a. Using the TEXTJOIN and INDIRECT Functions
The Generic Formula We are Going to Use:
=TEXTJOIN("",TRUE,IFERROR(MID(cell,ROW(INDIRECT("1:100")),1)+0,""))📌 Steps:
1. First, type the following formula in Cell C5:
=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:100")),1)+0,""))2. Next, press Enter.
3. Then, drag the Fill Handle icon over the range of cells C6:C9.
Finally, we are successful in removing non-numeric characters from the cells.
🔎 Breakdown of the Formula
Here, we are showing the Breakdown only for the first row
➤ 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 as a text. We are using TRUE to work with the values that remain.
b. Using the TEXTJOIN and SEQUENCE Functions to Remove Non-Numeric Characters from Cells in Excel
The Generic Formula We are Going to Use:
=TEXTJOIN("", TRUE, IFERROR(MID(cell, SEQUENCE(LEN(cell)), 1) *1, ""))📌 Steps:
1. First, type the following formula in Cell C5:
=TEXTJOIN("", TRUE, IFERROR(MID(B5, SEQUENCE(LEN(B5)), 1) *1, ""))2. Next, press Enter.
3. Then, drag the Fill Handle icon over the range of cells C6:C9.
In the end, we successfully removed non-numeric characters from the cells.
🔎 Breakdown of the Formula
Here, we are showing the Breakdown only for the first row
➤ SEQUENCE(LEN(B5))
This function returns us an array of {1;2;3;4;5;6;7;8;9;10;11}
➤ MID(B5, SEQUENCE(LEN(B5)), 1)
This function returns the array {“4″;”0″;”0″;”6″;”J”;”a”;”c”;”k”;”s”;”o”;”n”}
➤ MID(B5, SEQUENCE(LEN(B5)), 1) *1
This function returns {4;0;0;6;#VALUE!;#VALUE!;……….}
➤ IFERROR(MID(B5, SEQUENCE(LEN(B5)), 1) *1, “”)
After that, we will get this array {4;0;0;6;””;””;””;””;””;””;””}
➤ TEXTJOIN(“”, TRUE, IFERROR(MID(B5, SEQUENCE(LEN(B5)), 1) *1, “”))
In the end, the TEXTJOIN function will concatenate the non-empty values in the array and ignore empty values as ignore_empty argument is set to TRUE using an empty string (“”) for the delimiter.
2. Using VBA Codes 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:
1. First, press Alt+F11 on your keyboard. After that, it will open the Visual Basic Editor.
2. Next, click on Insert > Module.
3. After that, 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 Function4. Now, type the following formula in Cell C5:
=DeleteText(B5)5. Then, press Enter.
6. Finally, drag the Fill Handle over the range of cells C6:C9.
As you can see, we successfully deleted the non-numeric characters using the VBA codes.
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. And don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Related Articles
- How to Remove Characters from Left in Excel (6 Ways)
- Use TEXTJOIN Function in Excel (3 Examples)
- The COUNTIF Function to Count Blank Cells in Excel: 2 Examples
The post How to Remove Non-numeric Characters from Cells in Excel appeared first on ExcelDemy.
