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