Remove Numbers From Text in Excel & Google Sheets

This tutorial will demonstrate how to remove numbers from the text in a cell in Excel & Google Sheets.

We will discuss two different formulas for removing numbers from text in Excel.

SUBSTITUTE Function Formula

We can use a formula based on the SUBSTITUTE Function. It’s a long formula but it’s one of the easiest ways to remove numbers from an alphanumeric string.

In this formula, we have nested SUBSTITUTE functions 10 times, like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

Array TEXTJOIN formula

To remove numbers from alphanumeric strings, we can also use a complex array formula that consists of the TEXTJOIN, MID, ROW, and INDIRECT functions.

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

Note: TEXTJOIN is a new Excel Function available in Excel 2019+ and Office 365.

This is a complex formula, so we’ll divide it into steps to understand it better.

Step 1

The MID function is used to extract the alphanumeric string based on the start_num and num_chars arguments.

For the start_num argument in the MID function, we’ll use the resultant array list from the ROW and INDIRECT functions.

=ROW(INDIRECT("1:"&LEN(B3)))

And for the num-chars argument, we’ll put 1. After putting the arguments in the MID function, it will return an array.

{=MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)}

Step 2

We’ll add zero to each value in the resultant array (that we get from the above MID function). In excel, if add numbers to non-numeric characters, we’ll get a #VALUE! Error. So, after adding 0 in the above array, we’ll get an array of numbers and #Value! Errors.

{=MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0}

Step3

After adding 0, the resultant array is put into the ISERR function. As we know that ISERR function returns TRUE for errors and FALSE for non-error values.

So, it’ll give an array of TRUE and FALSE, TRUE for non-numeric characters, and FALSE for numbers.

=ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0)

Step 4

Now, we’ll add the IF function.

The IF function will check the result of the ISERR function (Step 3). If its value is TRUE, it will return an array of all the characters of an alphanumeric string. For this, we have added another MID function without adding zero at the end. If the value of the IF function is FALSE, it will return blank (“”).

In this way, we’ll have an array that contains only the non-numeric characters of the string.

=IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),"")

Step 5

Finally, the above array is put into the TEXTJOIN function. The TEXTJOIN function will join all the characters of the above array and ignore the empty string.

The delimiter for this function is set an empty string (“”) and the ignore_empty argument’s value is entered TRUE.

This will give us the desired result i.e. only the non-numeric characters of the alphanumeric string.

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

Note: This is an Array Formula. When entering array formulas in Excel 2019 or earlier, you must use CTRL + SHIFT + ENTER to enter the formula instead of the regular ENTER.

You’ll know you entered the formula correctly by the curly brackets that appear. DO NOT manually type the curly brackets, the formula will not work.

With Office 365 (and presumably versions of Excel after 2019), you can simply enter the formula like normal.

TRIM Function

When the numbers are removed from the string, we might have extra spaces left. To remove all the trailing and leading spaces, and the extra spaces between words, we can use the TRIM function before the main formula, like this:

=TRIM(C3)

Remove Numbers from Text In Google Sheets

The formula to remove numbers from the text works exactly the same in Google Sheets as in Excel: