Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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. Here is our overview image of removing non-numeric characters in Excel.

Overview Image


Download Practice Workbook

Please download this practice workbook


2 Easy 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.

Easy Ways to Remove Non-Numeric Characters from Cells in Excel

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.

Nesting TEXTJOIN and INDIRECT Functions to Delete Non Numeric Characters from Cells

Formula Breakdown

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.

Showing Results for Nesting TEXTJOIN and INDIRECT Functions to Delete Non Numeric Characters from Cells

Read more: How to Remove Specific Characters 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.

Opening Module to Apply VBA Code to remove Non Numeric Characters from Cells

  • 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

Applying VBA Code to Delete Non Numeric Characters from Cells

  • Thensave 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.

Applying VBA Code with formula

  • Therefore, drag the Fill Handle over the range of cells C6:C9.
  • Finally, you can see, we have successfully deleted the non-numeric characters using the VBA code.

Showing Results for Applying VBA Code

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

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.

4 Comments
  1. 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.

  2. 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.

  3. 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.

Leave a reply

ExcelDemy
Logo