Google Sheets: remove the same text or certain characters from multiple cells at once

Google Sheets: remove whitespace

Whitespace can easily slip into cells after the import or if multiple users edit the sheet at the same time. In fact, extra spaces are so common that Google Sheets has a special Trim tool to remove all whitespaces.

Just select all Google Sheets cells where you want to remove whitespace and choose Data > Trim whitespace in the spreadsheet menu:

As you click the option, all leading and trailing spaces in the selection will be taken away completely while all extra spaces in-between the data will be reduced to one:

Remove other special characters from text strings in Google Sheets

Alas, Google Sheets doesn't offer a tool to 'trim' other characters but spaces. You have to deal with formulas here.

Here I have addressed with hashtags before the apartment numbers and phone numbers with dashes and brackets in-between:

I will use formulas to remove those special characters.

The SUBSTITUTE function will help me with that. It is normally used to replace one character with another, but you can turn that to your advantage and replace the unwanted characters with… well, nothing :) In other words, remove it.

Let's see what argument the function requires:

• text_to_search is either the text to process or a cell that contains that text. Required.
• search_for is that character that you want to find and delete. Required.
• replace_with — a character you will insert instead of the unwanted symbol. Required.
• occurrence_number — if there are several instances of the character you're looking for, here you can specify which one to replace. It's completely optional, and if you omit this argument, all instances will be replaced with something new (replace_for).

So let's play. I need to find a hashtag (#) in A1 and replace it with 'nothing' which is marked in spreadsheets with double quotes (""). With all that in mind, I can build the following formula:

=SUBSTITUTE(A1,"#","")

Then copy this formula down the column if Google Sheets doesn't offer to do that automatically, and you'll get your addresses without the hashtags:

But what about those dashes and brackets? Should you create additional formulas? Not at all! If you nest multiple SUBSTITUTE functions in one Google Sheets formula, you will remove all these characters from each cell:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"#",""),"(",""),")",""),"-","")

This formula removes characters one by one and each SUBSTITUTE, starting from the middle, becomes the range to look at for the next SUBSTITUTE:

Remove specific text from cells in Google Sheets

Although you can use the aforementioned SUBSTITUTE function for Google Sheets to remove text from cells, I'd like to show another function as well — REGEXREPLACE.

Its name is an acronym from 'regular expression replace'. And I'm going to use the regular expressions to search for the strings to remove and replace them with ' nothing' ("").

As you can see, there are three arguments to the function:

• text — is where you're looking for the text string to remove. It can be the text itself in double quotes or a reference to a cell/range with text.
• regular_expression — your search pattern that consists of various character combinations. You'll be looking for all strings that match this pattern. This argument is where all the fun happens, if I may say so.
• replacement — a new desired text string.

Let's suppose my cells with data also contain the country name (US) if different places in cells:

How will REGEXREPLACE help me remove it?

=REGEXREPLACE(A1,"(.*)US(.*)","$1 $2")

Here's how the formula works exactly:

• it scans the contents of the cell A1
• for matches to this mask: "(.*)US(.*)"

  This mask tells the function to look for the US no matter what number of other characters may precede (.*) or follow (.*) the name of the country.

  And the entire mask is put to double quotes per the function demands :)

• the last argument — "$1 $2" — is what I want to get instead.$1 and $2 each represent one of those 2 groups of characters — (.*) — from the previous argument. You should mention those groups in the third argument this way so the formula could return everything that possibly stands before and after the US

  As for the US itself, I simply don't mention it in the 3rd argument — meaning, I want to return everything from A1 without the US.

Remove text before/after certain characters in all selected cells

Remove the first/last N characters from strings in Google Sheets

Whenever you need to remove a certain number of different characters from the beginning or the end of a cell, REGEXREPLACE and RIGHT/LEFT+LEN will also help.

So, how can I erase the codes from these phone numbers? Or, in other words, remove the first 9 characters from cells:

• Use REGEXREPLACE. Create a regular expression that will find and delete everything up to the 9th character (including that 9th character):

  =REGEXREPLACE(A1,"(.{9})(.*)","$2")

  Tip. To remove the last N characters, just swap the groups in the regular expression:

  =REGEXREPLACE(A1,"(.*)(.{9})","$1")

• RIGHT/LEFT+LEN also count the number of characters to delete and return the remaining part from the end or the beginning of a cell respectively:

  =RIGHT(A1,LEN(A1)-9)

  Tip. To remove the last 9 characters from cells, replace RIGHT with LEFT:
  =LEFT(A1,LEN(A1)-9)

• Last but not least is the REPLACE function. You tell it to take the 9 characters starting from the left and replace them with nothing (""):

  =REPLACE(A1,1,9,"")

  Note. Since REPLACE requires a starting position to process the text, it won't do if you need to delete N characters from the end of a cell.