How to Remove Non-numeric Characters from Cells in Excel

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.


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:

dataset to remove non numeric characters from cells in excel

Note: These formulas will only work in Excel 2019, 2021, and Excel 365.

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,""))

formula to count non numeric characters from cells in excel

2. Next, press Enter.

result without non numeric character

3. Then, drag the Fill Handle icon over the range of cells C6:C9.

drag the fill handle icon to copy formula

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, ""))

formula to remove non numeric characters from cells in excel

2. Next, press Enter.

result without non numeric characters

3. Then, drag the Fill Handle icon over the range of cells C6:C9.

drag the fill handle icon to copy formula

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.

Read more: How to Remove Specific Characters in Excel

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.

insert module for VBA codes

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 Function

4. Now, type the following formula in Cell C5:

Β =DeleteText(B5)

formula of VBA code

5. Then, press Enter.

result after entering the formula

6. Finally, drag the Fill Handle over the range of cells C6:C9.

excel remove non numeric characters from cells

As you can see, we successfully deleted the non-numeric characters using the VBA codes.

Read more: How to Remove Spaces in Excel: With Formula, VBA & Power Query


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

 

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo