How to Remove Extra Characters from Strings in Google Sheets

Removing repeated characters from the end of strings in your Google Sheets document makes it much easier to read and use your spreadsheet. In this article, we will show you how to remove extra characters from the end of strings that you might not need.

In this article, we will explore how to deal with the challenge of manipulating certain strings of text to remove or replace them. We will take on two different styles.

First, we will tackle the REGEXREPLACE function, which is a typical formula you can use and is part of the Google Sheets documentation.

Second, we will look into the Power Tools add-on for Google Sheets, which includes many useful functions but more importantly, for now, a solution for removing or replacing text or strings in cells.

The Anatomy of the REGEXREPLACE Function

The syntax (the way we write) the REGEXREPLACE function looks complicated, but it’s actually quite simple. it’s as follows

=REGEXREPLACE(text, regular_expression, replacement)

Let’s break this down to understand the syntax of REGEXREPLACE and each term meaning:

• = the equal sign is how we begin any function in Google Sheets.
• REGEXREPLACE is our function.
• text is the text, which will have a section replaced.
• regular_expression is the regular expression will all matching instances in the text will be replaced.
• replacement is the text which will be inserted into the original text.

Note that this function only works with text and string inputs (and not numbers) and returns text as an output.

Installing the Power Tools Add-on in Google Sheets

The second method of removing extra characters from the text is using a free add-on called Power Tools.

If you’re not familiar with how add-ons in Google Sheets works, here is a walkthrough of how you can add this to your Google Sheets set-up.

1. Click on Add-ons on the Menu Bar of your Google Sheets document. You might have some existing add-ons installed already. Choose “Get add-ons” from the drop-down menu, which will bring you to the G Suite Marketplace.

2. On the Search menu, look for Power Tools.

3. Click on the Power Tools icon.

4. Click Install. It will ask you for which Google Account you will connect the add-on to, as well as permission. Click Agree and it should install shortly.

5. You can go back to your Google Sheets document and the Power Tools add-on will be loaded into the Add-ons menu. There will also be a pop-up menu on the right side of the screen.

Power Tools should now be ready for your use. 😀

How to Remove Extra Characters from Strings in Google Sheets

Here are the two methods you can choose from to accomplish our task

Using REGEXREPLACE Formula

This is a simple formula that you can use to easily replace any text you’d like to remove within the cells of Google Sheets.

1. Choose the cell where you want your list to begin. Type in your equal sign ‘=‘.

2. Type in the name of the function REGEXREPLACE.

3. The drop-down menu will help you with the inputs that you need.

4. Put in the following arguments, given that our target string is in C3. Here, we replace “353 “ (including 1 space to remove that portion) with “” to remove it completely.

5. Hit Enter and apply the same formula to the rest of the column.

Using Power Tools Google Sheets Add-On

Here is a very easy way to remove unwanted strings. The Power Tools add-on makes this super easy.

1. Choose Power Tools from the Add-ons menu, and click on Start to bring up the pop-up menu.

2. Click the Text option, found in the middle of the menu.

3. Choose the Remove option, found on the top right.

4. Highlight the cells that you want the removal of text to affect.

5. On the pop-up menu, click on the option to “Remove substrings individually” Here, we choose to remove the international codes from the phone numbers. Click on the blue “Remove” button.

6. Here you should see that the numbers are gone from the highlighted cells.

This simple problem can be practiced. Use the link below to use our spreadsheet sample:

That’s all, good job! You can now replace or remove extra characters from strings in Google Sheets. Try using the skills you’ve learned from this post together with the other numerous Google Sheets formulas to create effective and powerful spreadsheets!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

Subscribe