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.
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.
Use this tab to delete single symbols or their combinations within the selected range:
Click the Remove button and the chosen chars will be erased from your spreadsheet.
Open the Remove spaces and delimiters group to see all the possible ways to delete excess blanks and delimiters in your sheet:
Check the boxes next to the data you'd like to delete in the range of interest:
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.
The options in this group will help you delete characters depending on where they are in the selected cells:
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.
Fine-tune the necessary option and click the Remove button to start the process.
Just commenting to let you know that using your tips my problem is solved. Thanks!
Hello, Subh! Thank you for your comment! We are glad to hear that our tools have been of help to you.
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
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:
2. Or completely remove the unwanted substrings &8364:
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),"")
Thank you for sharing your solution. We appreciate your time in doing that.
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.
If the string has the multiple special characters like below, how to remove this?
Vim Drop DwÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¢ÃƒÆ’Ã¢â‚¬Å¡Ãƒâ€šÃ‚Â Gel-Lmon, 115ml Pack
Thank you for contacting us. I have just replied to your query by email. Please provide us with the information requested in the email so that we’ll be able to help you better. Thank you.
This sure is a helpful tool.
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 (firstname.lastname@example.org) 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 email@example.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.
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!