This post explains that how to remove non-numeric characters (numbers) from a text string in one cell in excel 2016/2019/365. How to remove non numeric characters from a cell containing text string with an excel formula. And how to remove alphanumeric characters from a cell with a user defined function in excel VBA.

1. Remove non numeric characters with an Excel Formula

If you want to remove non numeric characters from a text cell in excel, you can use the array formula:

{=TEXTJOIN("",TRUE,IFERROR(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)+0,""))}

Let’s see how the above formula works:

=ROW(INDIRECT(“1:”&LEN(B1))

The ROW function returns the below array list:

{1,2,3,4,5,6,7,8,9}

=MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)

The MID formula will return the below array:

{"e","x","c","e","l","2","0","1","6"}

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

The array returned by the above MID function add zero for each value in array. If the value is a numeric text, it will be converted to text format. If not, returns empty string. So the IFERROR function returns the below array:

{2,0,1,6}

Last, the TEXTJOIN function join the values in above array returned by the IFERROR function.

2. Remove non numeric characters using VBA Code

You can create a new function to remove numeric characters from a cell that contain text string in Excel VBA. Just refer to the below steps:

Step1: open visual Basic Editor, then insert a module and name as : RemoveNonNum.

Step2: click “Insert“->”Module“, then paste the following VBA code into the window:

Step3: paste the below VBA code into the code window. Then clicking “Save” button.

Sub RemoveNonNum()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("select one Range that you want to remove non numeric characters", "RemoveNonNum", 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

Step4: back to the current worksheet, then run the above excel macro. Click Run button.

Step5: select one Range that you want to remove non numeric characters. click Ok button.

Step6: Let’s see the last result:

3. Video: Remove non numeric characters in Excel

This video will demonstrate how to remove non-numeric characters in Excel using a formula or VBA code.

4. Related Formulas

• Remove Numeric Characters from a Cell
  If you want to remove numeric characters from alphanumeric string, you can use the following complex array formula using a combination of the TEXTJOIN function, the MID function, the Row function, and the INDIRECT function..…
• Combine Text from Two or More Cells into One Cell
  If you want to combine text from multiple cells into one cell and you can use the Ampersand (&) symbol.If you are using the excel 2016, then you can use a new function TEXTJOIN function to combine text from multiple cells…

5. Related Functions

• Excel TEXTJOIN function
  The Excel TEXTJOIN function joins two or more text strings together and separated by a delimiter. you can select an entire range of cell references to be combined in excel 2016.The syntax of the TEXTJOIN function is as below:= TEXTJOIN  (delimiter, ignore_empty,text1,[text2])…
• Excel MID function
  The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
• Excel LEN function
  The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…
• Excel IFERROR function
  The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
• Excel ROW function
  The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel INDIRECT  function
  The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….