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.

Table of Contents

## 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])….

## Leave a Reply

You must be logged in to post a comment.