How to Remove Numbers from a String in Google Sheets

Unlike MS Excel, Google Sheets allows the use of regular expressions to find and directly work with alphanumeric values from a cell, of course with specific functions only. And with these functions, we will look to remove numbers from a string in Google Sheets in this article.

Let’s get started.

Table of Contents

2 Ways to Remove Numbers from a String in Google Sheets

1. Using the REGEXEXTRACT Function

The first regular expression function we have is the REGEXEXTRACT function.

REGEXEXTRACT function syntax:

REGEXEXTRACT(text, regular_expression)

It is a simple function that retrieves the first matching values defined by the user as a regular expression.

Consider the following dataset:

We want to remove the numbers from the string and extract everything else.

With regular expressions being highly customizable, we can approach finding only the text from the String column in three ways:

\D: This means non-digit. Finds all characters that are not digits. Also includes other special characters. Note: Alternatively, \d is for digits only.
[a-zA-Z]: Finds all alphabetic characters only regardless of case.
[[:alpha:]]: Finds all alphabetic characters only (another version).

You can use either to extract non-digit characters from the alphanumeric string using the REGEXEXTRACT function.

Step 1: Open the REGEXEXTRACT function and refer to the cell that contains the alphanumeric string.

Step 2: Use either of the regular expressions to extract the alphabet characters from the string. We have used \D+. The plus symbol (+) at the end allows us to also extract any adjacent characters that match the condition. It is important for this function.

Step 3: Close parentheses and press ENTER.

=REGEXEXTRACT(B2,"\D+")

Now, if we are to use the fill handle to apply the formula to the rest of the column:

We sometimes do not get the complete string with REGEXEXTRACT.

Limitations of REGEXEXTRACT

Recall the definition of the REGEXEXTRACT function: it retrieves the first matching values only.

So, only the first instances that match the regular expression condition will be extracted by the REGEXEXTRACT function. Making the function ignore the alphabet characters after the removed numbers.

However, we can take advantage of this limitation to add more regular expression conditions to find, for example, strings that begin with text:

=REGEXEXTRACT(B2,"^\D+")

Or strings that end with a text:

=REGEXEXTRACT(B2,"\D+$")

Simply put, the number values act as delimiters from which the characters of the string will be removed.

Similar Readings

2. Using the REGEXREPLACE Function

When it comes to removing all numbers from a string in Google Sheets, there is no better option than using the REGEXREPLACE function.

The REGEXREPLACE function syntax:

REGEXREPLACE(text, regular_expression, replacement)

Here, instead of extracting non-numerical values from the string to give an idea of removing numbers as we have seen with REGEXEXTRACT, we can use REGEXREPLACE to directly replace number values with something else.

Thus, to remove number values, we can simply find all numerical values in a string and replace them with a blank using REGEXREPLACE.

We can find numbers with the following regular expressions:

\d
[0-9]
[[:digit:]]

So, to remove all numbers from a string in Google Sheets using REGEXREPLACE:

Step 1: Open the REGEXREPLACE function and reference the cell that contains the string.

Step 2: enter the regular expression to find all numerical values. We have used [0-9] in this case.

Step 3: Enter the replacement value. Simply entering quotation marks (“”) will replace the number values with blanks, thus removing them from the string.

You can also opt to ignore this field to get the same result as “replacing with blanks”.

Step 4: Close parentheses and press ENTER. Use the fill handle to apply the formula to the rest of the column.

=REGEXREPLACE(B2,"[0-9]","")

We have successfully removed all numbers from a string using the REGEXREPLACE formula.

So, whether it be for text or numbers, using REGEXREPLACE is the simplest approach.

The Advantage of Using Regular Expressions to Remove Numbers from a String in Google Sheets

We mentioned in our introduction that the biggest advantage that Google Sheets has over Excel is its ability to work with regular expression functions. Let’s see what happens when we try to remove numbers from a string without their use.

The simplest alternative would be to use the SUBSTITUTE function to take a number value and replace it with a blank.

In the image above you can see that to remove two number values we have to nest one SUBSTITUTE function in another:

=SUBSTITUTE(SUBSTITUTE(B3,"8",""),"2","")

This means that to remove all ten instances or number values (0 to 9) from a string in Google Sheets, we must include ten instances of SUBSTITUTE functions!

While there are alternatives to using text functions to make compound formulas and even Apps Script, they can prove equally, if not more, complex

On the other hand, we can simply use a function like REGEXREPLACE to remove these characters in minimal time.

Final Words

That concludes some of the best and simplest ways to remove numbers from a string in Google Sheets.

Thanks to the application allowing users to use regular expression functions things have become much easier when it comes to searching for and working with characters and strings.

Feel free to leave any queries or advice you might have for us in the comments section below.