Remove extra spaces, chars, and substrings

Power Tools provides you with various utilities to speed up most of your daily tasks. Learn how to work with the Remove group to delete extra spaces and different characters in your Google spreadsheet.

Video: How to remove characters and substrings in Google Sheets

Before you start

If you remove characters from cells formatted as dates, time, currencies, etc., Google Sheets may change the format of the resulting cells according to their new contents.

How to remove unwanted spaces and characters

Run the Remove tool

  1. Go to Extensions > Power Tools > Start to open the add-on in Google Sheets:
    Run Power Tools.
  2. Access the Text group on the add-on sidebar:
    Access the Text group on the Power Tools sidebar.
  3. Click on the Remove icon to run the tool:
    The Remove icon.
  4. Select the range with your data and choose between three ways of clearing the selected range.

Remove substrings or individual characters

Use this tab to delete single symbols or their combinations within the selected range:
Get rid of unwanted characters or substrings.

  • Remove substrings individually
    Enter the combination(s) of characters you need to delete, e.g. words you want to remove from cells. If their text case should be considered as well, also select the Match case option.

    Tip. To remove multiple different substrings at once, enter them one per line. E.g. +1 and +44 will clear out all instances of these country codes.
  • Remove entered characters individually
    This option lets you list all characters to delete in one go. E.g. entering 1a will remove all occurrences of character 1 and of character a.

Click the Remove button and the chosen chars will be erased from your spreadsheet.

Remove spaces and delimiters

Open the Remove spaces and delimiters group to see all the possible ways to delete excess blanks and delimiters in your sheet:
Delete extra spaces, delimiters, and non-printing characters.
Check the boxes next to the data you'd like to delete in the range of interest:

  • Remove leading and trailing spaces
    Eliminate all extra spaces before and after the values in your cells that may impede with further calculations.
  • Remove spaces between words to one
    Ticking off this option will help you get rid of any extra blanks between words in the selected cells.
  • Remove all spaces
    The add-on will delete all spaces in the selected range, including non-breaking ones.
  • Remove line breaks
    If line breaks are also unwelcome in your data, this option will deal with them in all selected cells in one go.
  • Remove html entities
    This one will clear away all HTML entities like " " that appear in your data after importing them from web sources.
  • Remove html tags
    Remove all HTML tags whether paired (e.g. <ul></ul>) or unpaired (e.g. <br>), opening or closing that you may have pulled from the web as well.
  • Remove all delimiters
    Delete any line breaks, spaces, commas, and semicolons used as delimiters in your range in Google Sheets.
  • Remove non-printing characters
    Check this option to get rid of all non-breaking spaces, line breaks, and tabs in the range.

The last checkbox — Ignore non-text format and formulas — can be applied to any other setting within the group and is designed to avoid data corruption. Tick it off to make sure no cells with formulas, dates, time, currencies and other non-text format is changed.

Click the Remove button and see the characters you selected are erased from your spreadsheet.

Remove characters by position

The options in this group will help you delete characters depending on where they are in the selected cells:
Get rid of characters based on their position in cells.

  • Remove characters by position
    Choose this radio button to cut characters from certain positions in the selected cells by their numerical order. For example, to clear telephone extensions from the numbers that take 10-14 characters in a column, enter the first and the last character position you'd like to delete in the From and To fields respectively.
  • Remove the first/last characters
    This one will help you delete any number of characters at the beginning of the selected cells or at their end.

    Pick the necessary option in the drop-down list: The first to delete text that starts the cells, and The last to remove the ending; then enter the number of characters you'd like to delete.

  • Remove characters before/after text
    Select this option to delete any information before or after a specific character or string in the range of your choice:

    • Set before in the drop-down list and enter the string before which you'd like to remove all text.
    • Choose after in the drop-down list and use the text field to enter the string after which you'd like to have all values deleted.
    Tip. The Match case option here will make sure that you remove the text before/after a string with a certain text case.

Fine-tune the necessary option and click the Remove button to start the process.

Responses

Hi, I'm importing values into sheets from gravity forms. By default it imports the euro sign as &8364; I am auto importing the data into the sheet with zapier. I would like to be able to apply a filter (or something like) that auto removes the &8364; leaving only the values. Is it possible to apply something like this to auto run on a column?

Thanks so much for your considerations

Hi Maria,

Thank you for contacting us. In this case, you can try out the following add-ons:
1. Feel free to replace codes, e.g. &8364, with Euro symbols using the 'Replace codes with symbols' option described in this help page:
https://www.ablebits.com/docs/google-sheets-replace-text/#replace-symbols

2. Or completely remove the unwanted substrings &8364:
https://www.ablebits.com/docs/google-sheets-remove-unwanted-characters/#delete-chars-substrings

Both tools will work with any range you select at a time. As for autorun, currently only simple one-step operations and scenarios for Remove Duplicates and Merge Sheets can be added to the list of Recent / Favorites tools. You see, usually add-ons have 2-5 steps that have different settings depending on the spreadsheet where you run the add-on.

We do consider adding triggers for such tools but I can't tell you the exact timing yet. I can contact you back when the functionality is supported for such tools.

I ran into a more complex problem. I needed to switch the last name and first name (because they were listed as "Owens Bronson, Jenny Olivia" [Yes! that complex!]) and also take out the middle name... and on top of that I needed it to dynamically reference cells because those cells were linked to another document. And... on top of that, many of the last names were double last names. I ended up coming up with the formula (which is a combination of some formulae I found and some personal trial and error) and I thought it might help someone:
=IF(ISTEXT(TRIM(MID(SUBSTITUTE(E53," ",REPT(" ",LEN(E53))), FIND(",",SUBSTITUTE(E53," ",REPT(" ",LEN(E53))))+LEN(E53)+1, LEN(E53)))&" "&LEFT(E53,FIND(",",E53)-1)),TRIM(MID(SUBSTITUTE(E53," ",REPT(" ",LEN(E53))), FIND(",",SUBSTITUTE(E53," ",REPT(" ",LEN(E53))))+LEN(E53)+1, LEN(E53)))&" "&LEFT(E53,FIND(",",E53)-1),"")

Hi Tim,

Your formula is impressive, thank you for sharing it! But if you're okay with using add-ons, we have a couple of them that can solve such a task by parts without any formulas :) You can find them all in Power Tools: they are Split Names, Merge Values, and Remove Duplicates.

You can install a 30-day trial version of Power Tools to see if it suits your needs, or email us so we could help you out with steps to follow.

Hi,

If the string has the multiple special characters like below, how to remove this?

Vim Drop Dwâ  Gel-Lmon, 115ml Pack

Kelly Andino says:
May 31, 2019 at 12:27 am

I have some blank cells in a shared google sheet that has a color around it, and when you hover over it, it says Anonymous Chipmunk. I want to know how to delete that box around the empty shell.

Thank you for your interest in our tools, Kelly.

I'm afraid your task is not entirely clear. For us to be able to suggest you, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.

Note. We keep support@4-bits.com for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm in this message thread.

We'll look into your task and see if our software can help.

Is there way to customize what trailing character is removed? To remove, say, a trailing slash or comma? The lengths of the strings vary, so I can't do 'crop after character 10,' for example.

Thank you for your interest in our add-on, Joanne.

It is possible to remove a substring or individual character(s) using our Remove tool, but the add-on will delete all the characters you specify, not only the trailing ones. As a workaround, you can use our Advanced Find and Replace add-on to find all the unwanted symbols and replace only the necessary ones with "nothing" to get rid of them.

Feel free to contact us again if you have any other questions or need further assistance.

Dhiren Bavisi says:
January 30, 2019 at 4:20 am

How do I remove multiple substring texts from a section, instead of one by one?

For example, I have a column A filled with texts that I no longer want to see in the substrings of column B,C and D.

Thank you for your question, Dhiren.

I'm afraid at the moment it's impossible to remove multiple different strings at a time.
You can only remove them one by one from all columns if you select them (these columns) as the range to process.

However, this possibility looks really good, and I will forward this feature request to our developers and executives to consider. I can't give you any promises but can contact you back when/if it's supported.

I sincerely apologize for any inconvenience.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.