How to Remove Numeric Characters from a Cell

This post explains that how to remove numeric characters (numbers) from a text string in one cell in excel 2016. how to remove only numeric characters but keep the non-numeric characters from a cell containing text string with an excel formula. And how to remove numbers from a cell with a user defined function in excel VBA.

Remove numeric characters from a cell containing text

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.

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

Let’s see how the above array formula works:

The MID function will extract the sub strings based on the start_num and num_chars arguments. And the INDIRECT function within ROW function will returns an array list, like below:

{1,2,3…} and the length of array is the length of CELL B1.

The MID function will use the result returned by the ROW function to extract the sub string from Cell B1, as the results by the ROW function is an array, so the MID function also returns an array list. Like below format:

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

The result returned by MID function add zero for each value in array, if the value is non-numeric value, then the ISERR function return a #VALUE error. Or return the numeric text values will be converted to number.

the IF function will check if the result returned by ISERR function, If TRUE, then return an empty string, otherwise, return that non-numeric character using the second MID function.

{"e","x","c","e","l","","","",""}

Last, the TEXTJOIN function will join all values and ignore the empty string.

remove numeric characters from a cell1

Remove numeric characters from text using a user defined function in VBA

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:

1# open visual Basic Editor, then insert a module and name as : RemoveNumericCharacters.

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

remove numeric characters from a cell2

Function RemoveNum(Txt As String) As String
    With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[0-9]"
    RemoveNum = .Replace(Txt, "")
    End With 
End Function

3# save the user defined function.

4# enter into the below formula in Cell C1.

=RemoveNum(B1)

remove numeric characters from a cell3


Related Formulas

  • remove non numeric characters from a cell
    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,””))}…
  • 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…

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 IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • 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])….
  • Excel ISERR function
    The Excel ISERR function returns TRUE if the value is any error value except #N/A.The syntax of the ISERR function is as below:= ISERR (value)…

 

 

Related Posts
How to Extract First Letter from Each Word in a Cell in Excel
extract first letter from word3

This post will guide you how to extract first letter from each word in a given cell in Excel. How do I extract the first letter of each word in a range of names in your current worksheet in Microsoft ...

How to ignore Blank Cells while Concatenating cells in Excel
concatenate cell but blanks3

This post will guide you how to concatenate cells but ignore all blank cells in your worksheet in Excel. How do I concatenate cells but ignore blank cells with a formula in Excel. How to create a concatenate formula to ...

How to Calculate the Average Excluding the Smallest & Highest Numbers in Excel
Calculate the Average Excluding the Smallest & Highest 10

Calculating the average for a batch of data is frequently used in our daily life. But for some cases like statistic the average score in a competition, or price analysis, we often calculate the average excluding the smallest and highest ...

How to Get the Maximum or Minimum Absolute Value in Excel
Get the Maximum or Minimum Absolute Value 10

It is easy to find the maximum or minimum value in a batch of data in excel, but if this batch of numbers contains both positive and negative numbers, the maximum or minimum absolute value cannot be found out by ...

How to Count Only Numbers in Bold in a Range of Cells in Excel
Count Only Numbers in Bold in a Range of Cells2

This post will guide you how to Count only numbers with bold style in a range of cells in Excel. How do I Count on cells with bold font within a range of cells using User defined function in Excel ...

How to Sum Only Numbers in Bold in a Range of Cells in Excel
Sum Only Numbers in Bold in a Range of Cells2

This post will guide you how to sum only numbers with bold style in a range of cells in Excel. How do I sum on cells with bold font within a range of cells using User defined function in Excel ...

How to Filter Formula in Excel
filter formula4

This post will guide you how to filter your data with an Formula in Excel. How do I filter cells with formulas through a User defined function in Excel 2013/2016. Filter Formula Assuming that you have a list of data ...

How to Count Cells by Font Color in Excel
count cells by font color2

This post will guide you how to count cells by font color in Excel. How do I Count the number of cells with specific cell Font color by using a User Defined function in Excel. Count Cells by Font Color ...

How to Highlight Cells Containing Formulas in Excel
highlight cells contain formula7

This post will guide you how to highlight cells in which contain formulas using Conditional Formatting feature in Excel. How do I conditionally format a cell if it contains formula using a User defined function in combination with Conditional Formatting ...

How to Extract Number from Text String in Excel
extract number from text string3

This post will guide you how to extract number from a given test string in Excel. How do I extract all numbers from string using a formula in Excel. How to get all number from a given test string using ...

Sidebar