Learn formulas and formula-free ways to trim whitespaces, remove special symbols (even the first/last N characters) and the same text strings before/after certain chars from multiple cells at once.
Removing the same part of the text from several cells at once can be as important and tricky as adding it. Even if you know some of the ways, you will definitely find new ones in today's blog post. I share plenty of functions and their ready-made formulas and, as always, I save the easiest — formula-free — for last ;)
I'm going to start with the standard functions for Google Sheets that will remove your text strings and characters from cells. There's no universal function for this, so I will provide different formulas and their combinations for various cases.
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:
Alas, Google Sheets doesn't offer a tool to 'trim' other characters but spaces. You have to deal with formulas here.
Tip. Or use our tool instead — Power Tools will free your range from any characters you specify in a click, including whitespace.
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:
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:
Tip. The hashtag is also in double quotes since this is the way you should mention text strings in Google Sheets formulas.
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:
This formula removes characters one by one and each SUBSTITUTE, starting from the middle, becomes the range to look at for the next SUBSTITUTE:
Tip. What's more, you can wrap this in ArrayFormula and cover the entire column at once. In this case, change the cell reference (A1) to your data in column (A1:A7) as well:
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' ("").
Tip. If you're not interested in using regular expressions, I describe a much easier way at the end of this blog post.
Tip. If you're looking for ways to find and remove duplicates in Google Sheets, visit this blog post instead.
As you can see, there are three arguments to the function:
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?
Here's how the formula works exactly:
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 :)
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.
Tip. There's a special page you can reference to build various regular expressions and look for the text in different positions of cells.
Tip. As for those remaining commas, the SUBSTITUTE function described above will help to get rid of them ;) You can even enclose REGEXREPLACE with the SUBSTITUTE and solve everything with one formula:
When it comes to getting rid of everything before and after certain characters, REGEXREPLACE also helps. Remember, the function requires 3 arguments:
And, as I mentioned above when I introduced the function, it's the second one you should use correctly so the function knows what to find and remove.
So how do I remove the addresses and keep only phone numbers in cells?
Here's the formula I will use:
In the first part — .*\n.* — I use backslash+n to tell that my cell has more than one row. So I want the function to remove everything before and after that line break (including it).
The second part that is in brackets (\+.*) says that I want to keep the plus sign and everything that follows it intact. I take this part in brackets to group it and keep it in mind for later.
Tip. The backslash is used before the plus to turn it into a character you're looking for. Without it, the plus would be just a part of the expression that stands for some other characters (as an asterisk does, for example).
In a similar fashion, you can delete all phone numbers yet keep the addresses:
Only this time, you tell the function to group (and return) everything before the line break and clear out the rest:
There are a few more Google Sheets functions that let you remove the text before a certain character. They are RIGHT, LEN and FIND.
Note. These functions will help only if the records to keep are of the same length, like phone numbers in my case. If they're not, just use the REGEXREPLACE instead or, even better, the easier tool described at the end.
Using this trio in a particular order will help me get the same result and remove the entire text before a character — a plus sign:
Let me explain how this formula works:
Unfortunately, this way won't help much to remove the text after the line break in my case (clear phone numbers and keep addresses), because the addresses are of different length.
Well, that's all right. The tool at the end does this job better anyway ;)
Note. Since I already introduced these functions above, I will keep this point short and provide some ready-made formulas. Or feel free to hop to the easiest solution described at the very end.
So, how can I erase the codes from these phone numbers? Or, in other words, remove the first 9 characters from cells:
Tip. To remove the last N characters, just swap the groups in the regular expression:
Tip. To remove the last 9 characters from cells, replace RIGHT with LEFT:
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.
Functions and all is good whenever you have time to kill. But do you know there's a special tool that embraces all of the aforementioned ways and all you are to do is select the required radio button? :) No formulas, no extra columns — you couldn't wish for a better sidekick ;D
You don't have to take my word for it, just install Power Tools and see it for yourself:
Another tool from Power Tools will remove time and date units from timestamps. It's called Split Date & Time:
What's the splitting tool got to do with removing time and date units? Well, to remove time from timestamps, select Date since it's a part you want to keep and also tick off Replace source data, just like on the screenshot above.
The tool will extract the date unit and replace the entire timestamp with it. Or, in other words, this add-on for Google Sheets will remove the time unit from the timestamp:
You can have all these and over 30 other time-savers for spreadsheets by installing the add-on from the Google Store. The first 30 days are completely free and fully functional, so you have the time to decide if it's worth any investment.
If you have any questions related to any part of this blog post, I'll see you in the comments section below!
Table of contents