Remove Non-numeric Characters from a Cell in Excel Office 365!!

Do you know how to remove non-numeric characters from a cell in Excel? Here, we come up with the instructions to remove alphanumeric characters easily in the Excel worksheet. We have illustrated two examples using a formula and the VBA function. Let’s see them one-by-one in this article.

Remove Non-numeric Characters using Formula:

If you want to eliminate non-numeric characters from a text cell, then you can use the below formula.

=TEXTJOIN(“”,TRUE,IFERROR(MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)+0,””))

• TEXTJOIN function – The Excel TEXTJOIN Function concatenates or joins values with a given delimiter separating each value. This function will efficiently concatenate the ranges if the delimiter is an empty text string.
• TRUE function – It returns the value TRUE if the given conditions will be TRUE or Vice Versa.
• IFERROR function – The Excel IFERROR function returns a custom result when a formula generates an error and a standard result when no error is detected.
• MID function – It extracts the number (starting from the left side) or characters from the given string.
• ROW Function – The ROW function returns the row number for a reference.
• INDIRECT function – The INDIRECT Function in Excel returns a valid reference from a given text string.
• LEN function – Use the LEN function to find the length of the text string.

Example 1:

• First, you need to open the workbook.
• Then you have to enter the above-given formula in cell C1.

• After pressing the Enter key, you will get the result as shown in the below image.

Delete Non-numeric Characters using VBA:

Let’s see the steps to delete non-numeric characters using the VBA function.

Example 2:

• First, you need to open the Visual Basic Editor by clicking theALT + F11 Keys.
• Then, you need to go to the insert tab and select the module option from the menu.

• After that, enter the below VBA codes in the code window.

VBA code to remove non-numeric characters

Sub RemoveNonNumber()

Set myRange = Application.Selection

Set myRange = Application.InputBox(“select a Range that you want to remove non-numeric characters”, “RemoveNonNumber”, myRange.Address, Type:=8)

For Each myCell In myRange

LastString = “”

For i = 1 To Len(myCell.Value)

mT = Mid(myCell.Value, i, 1)

If mT Like “[0-9]” Then

tString = mT

Else

tString = “”

End If

LastString = LastString & tString

Next i

myCell.Value = LastString

Next

End Sub

• Finally, you need to save the code.

• Then get back to the workbook and select the Macros option under the Developer tab.

• After that, it will display the Macro dialog box.
• You need to select the Macros name and click the Run Button.

• You need to select the range that you want to remove non-numeric letters.

• It will display the result as shown in the below image.

Conclusion:

From the above article, we explained how to Remove Non-numeric characters from a cell in Excel Office 365 in a simple way. Please feel free to share your query or feedback in the below comment section. Thank you so much for reading!! To learn more, check out our website *Geek Excel!! *