How to Remove Characters from a String in Google Sheets (6 Easy Examples)

Updating data can involve both adding and removing other data to it. Things become trickier when we have hundreds of updates to perform, which is a common scenario in a spreadsheet. So, in this article, we will focus on how to remove characters from string data in Google Sheets.

We have a few scenarios prepared that should cover the fundamentals of removing specific characters from a string.

Let’s get started.

Table of Contents

6 Examples of How to Remove Characters from a String in Google Sheets

1. Remove N-Number of Characters from the Beginning of the String

Things become quite easy when you have a specific direction to remove characters from. In this case, it’s the beginning of the string.

From the following worksheet, we will try to remove the first four characters from each cell. That is, the three letters and the hyphen, leaving only the numbers:

We only need two simple functions to get the job done: RIGHT and LEN.

And the formula is simply:

=RIGHT(B2,LEN(B2)-4)

Where:

LEN calculates the number of characters in the string of cell B2.
The “-4” is the number that is to be subtracted from LEN. The “4” determines the number of characters that are to be removed from the string.
RIGHT presents the number of characters from the right side of the string according to “LEN(B2)-4”.

Of course, use the fill handle to apply the formula to the rest of the column.

2. Remove N-Number of Characters from the End of the String

Removing the n-number of characters from the end of a string should be the logical opposite of the previous section.

That said, this time we will look to remove the last few characters of a string, and for this example, it will be the last 5 characters (get ABC from ABC-1234).

For this approach, we will use the LEFT function as we are starting from the beginning of the string this time, along with the LEN function. And since we are removing the last five characters, we will subtract the LEN value by 5.

So, our formula will be:

=LEFT(B2,LEN(B2)-5)

3. Remove Characters from a String with the REPLACE function of Google Sheets

Alternatively, we can also use the REPLACE function to do the same: remove characters from a string in Google Sheets.

The REPLACE function only requires the position of the starting character and the number of characters that are to be removed. We can see this in the function’s syntax:

REPLACE(text, position, length, new_text)

In our case, the “text” field will refer to the cell that contains the string, cell B2.
The “position” field refers to the position of the first character that is to be removed. Where 1 means the first character of the string.
The “length” field takes an integer number to determine the number of characters to replace.
And finally, the “new_text” field is for our replacement value. If you want to remove the characters completely, simply input double quotation marks (“”).

So, let’s now perform our previous two examples with the REPLACE function.

First, we have the condition to remove the first 4 characters from the beginning of the string. The formula is:

=REPLACE(B2,1,4,"")

Second, we have the condition to remove the last 5 characters from the end of the string. The formula is:

=REPLACE(B2,4,5,"")

4. Remove Characters from a String after a Specific Character or Blank Space in Google Sheets

So far, we have looked at methods to remove characters from either end of a string. Let’s say that now we want to remove characters depending on certain conditions, for example, remove characters from after or before whitespace in a string in Google Sheets.

We can easily do this with the help of the MID function combined with FIND.

The MID function syntax:

MID(string, starting_at, extract_length)

For this example, we will use the following worksheet of names:

We will use this information to remove the last names and first names and put them in their respective columns.

To retrieve only the first names, we must remove all characters after the whitespace.

Step 1: Open the MID function and select the source string reference. In this case, it is cell B2.

Step 2: Since we will start from the beginning, the index will be 1.

Step 3: The delimiter is the whitespace. To search for this whitespace, we will use the FIND function on cell B2. This will return the index number of the whitespace. Subtract this index number by 1 to also remove the whitespace.

Step 4: Close parentheses and press ENTER to see the results. Use the fill handle to apply the formula down the column.

=MID(B2,1,FIND(" ",B2)-1)

And as for the formula to remove the first name:

=MID(B2,FIND(" ",B2)+1,999)

Here, our starting point is from the whitespace. The “+1” after FIND is to ignore the whitespace. The “999” is a placeholder number to include all characters.

Similar Readings

5. Use the SUBSTITUTE function to Remove Special Characters in Google Sheets

In the previous examples, we have removed characters of specific ranges from a string. Now, we will look at how we can scan the entire string and remove specific characters from it.

We can easily do this by using the SUBSTITUTE function.

SUBSTITUTE(text_to_search, search_for, replace_with)

The function essentially looks for a certain value in a string and replaces it with another.

Consider this example: We want to remove all instances of “?” from the Name column of the following worksheet:

The SUBSTITUTE function will look something like this:

=SUBSTITUTE(B2,"?","")

Where we are replacing the question mark (“?”) with blanks (“”).

But what if we wanted to remove both “?” and “!” from the string?

Unfortunately, SUBSTITUTE does not allow us to input multiple conditions in the same function. The solution to this is to add another SUBSTITUTE function around the first to include another condition.

Thus, we are essentially nesting one SUBSTITUTE formula in another:

=SUBSTITUTE(SUBSTITUTE(B2,"?",""),"!","")

The downside of this method is that the more individual characters you want to remove, the more SUBSTITUTE functions you have to nest.

6. Use the REGEXREPLACE Function to Remove Special Characters

While the REGEXREPLACE function is much like SUBSTITUTE, it has the advantage of being able to use regular expressions.

REGEXREPLACE(text, regular_expression, replacement)

This use of regular expressions allows users to greatly customize the character or text search conditions in a cell.

In the previous section, we saw that to remove multiple conditions, we had to nest a separate SUBSTITUTE function for each condition. However, with REGEXREPLACE, we can include multiple character conditions in a single field with the help of regular expressions.

The regular expression condition to find “?” and “!” in a string is:

"[?!]"

Thus, the REGEXREPLACE formula to remove these characters from a string in Google Sheets is:

=REGEXREPLACE(B2,"[?!]","")

But what if you had more special characters that you wanted to remove?

Alternatively, you can use the following formula to only include alphabet characters in your string, removing all others:

=REGEXREPLACE(B2,"[^A-Za-z]+","")

However, this will also remove all whitespaces.

Final Words

This concludes all the ways we can use to remove characters from a string in Google Sheets. As you have seen, there are multiple ways you can approach this as it completely depends on the scenario and requirements.

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