Despite lots of perks offered by Google Sheets, it also has its drawbacks. A clear example of that is the shortage of simple tools to manage text. Are we bound to add or replace text in Google Sheets manually or with complex formulas? Not anymore. :) We've filled this gap with simple one-click tools. Allow me to introduce them to you in this blog post.
All the tools I feature today are part of one utility — Power Tools. It is a collection of all our add-ons for Google Sheets. I highly encourage you to install it, be your own chef, and mix and match the below "ingredients" on your data. ;)
Many of us come to the point of compromising on the consistent style of tables for the sake of saving time. Thus, sooner or later, you find the data in your sheets in different cases and with excess characters typed in haste. This may turn out to be a problem especially if several people have the right to edit the same spreadsheet.
Whether you're a perfectionist who tends to keep data super clear and practical, or simply have to demonstrate data from your spreadsheets, the following tools will help.
Standard ways of changing cases in Google Sheets include functions: LOWER, UPPER, PROPER. To use them, you will have to create a helper column, build formulas there, and reference your original column. Then somehow convert formula results to values and remove the original column.
Well, you don't need to do any of the above with our tool. It quickly changes the case in your Google Sheets in the original cells themselves.
Tip. Watch this video to get to know the tool better, or feel free to read the short introduction right below it.
You will find the tool in the the Text group > Modify:
To change case in your spreadsheet with this add-on, just select the range with your text and pick the way to modify data: turn everything into Sentence case., lower case or UPPER CASE, Capitalize Each Word (aka proper case), lower & or tOGGLE tEXT.
Tip. If you're not sure what option you need to use, check out the help page for the tool where we described everything in detail.
Once you're ready, press Modify and watch your original data change the case:
If you import data from the web, you may find accented letters in your table like ß, Ö, or ç. The imported file may also contain different special characters: copyright signs (©), inverted question marks (¿), ampersands (&), and smart quotes (“ ”). These symbols may also be represented by their codes (often used on the web.)
If you try to replace them using the standard Google Sheets Find and replace tool (Ctrl+H), prepare to go over the replacing process for each character. You will also have to enter symbols you want to see instead.
Our Replace symbols utility is much faster and easier to use. It scans the selected data range and automatically replaces all accented characters or codes with their corresponding standard symbols.
Tip. The tool also resides in Power Tools: Text > Modify.
Here's also what you can do with codes and special characters using the same add-on:
And here you can see how the Replace smart quotes with straight quotes tool works (currently for double-quotes only):
If the modifications above are too much for your table, and you'd rather simply brush your Google Sheets text here and there, the add-on will help you automate this, too.
The Polish text tool looks through the range you select and does the following:
You are free to go with all three options at once or pick the one that suits your table best:
A standard method of adding text in Google Sheets is the same as always: a function. And it is CONCATENATE that usually inserts extra characters to your existing text.
But when it comes to functions, it always comes to an additional extra column for the formulas. So why bother adding special columns and formulas if there are add-ons that handle text right where it's at?
One of our tools is designed exactly for this task. It's called Add text by position and nests in the same Text group of Power Tools.
Tip. Watch this video to get to know the tool better, ot feel free to read the short introduction right below it.
It lets you not only add text in Google Sheets but also insert special characters and their combinations to your table, such as punctuation marks, a number sign (#), a plus sign (+), etc. And what's even better, you decide on the position for these new characters.
The first two options make it possible to add text at the beginning and at the end of all selected cells.
Let's say you'd like to supply your list of phone numbers with country codes. Since the code should precede the entire number, the task is to add numbers at the beginning of Google Sheets cells.
Just select the range with numbers, enter the desired country code into the corresponding field in the tool, and click Add:
The last three options of the tool let you insert characters depending on specific text in cells.
There, area codes for the US and Canada numbers start from the 3d character: +12025550198. So I need to add a round bracket before it:
Once added, the area codes end with the 6th character: +1(2025550198
Thus, I add a closing bracket after it as well. Here's what I've got:
These options will help me make the phone numbers even more readable by adding spaces before and after the brackets:
But what if adding text in Google Sheets is not an option and you'd rather delete some excess chars and obsolete text? Well, we have the tools for this job as well.
Tip. There is a help page for the Add text options as well, you'll find it here.
Sometimes white spaces and other characters may creep into your table. And once they get in, it may become pretty nerve-racking to track and eliminate them all.
The standard Google Sheets Find and replace utility will only replace one excess character with another. So in cases like this, it's better to delegate the duty to add-ons from the Remove group in Power Tools:
Tip. The Remove group also owns a help page where all the tools and their options are mentioned.
Feel free to watch this demo video as well:
This first tool gets rid of one or a few single characters and even Google Sheets substrings within the selected range. To be more exact, you can make it delete the following:
I'll take the same phone numbers from the previous example and remove all country codes and brackets at once with the tool:
The next utility for Google Sheets removes white spaces before, after, and within the text. If spaces are not welcome in your data at all, feel free to delete them completely:
The add-on also removes such special characters as commas, semicolons, and other delimiters (there's even a special checkbox for line breaks); non-printing characters (like line breaks), HTML entities (codes that are used instead of chars themselves), and HTML tags:
Sometimes though it is not the characters themselves that matter but their position in cells.
I will use this position to remove the extensions from all numbers with the corresponding tool:
Here's how the numbers transform in just a couple of clicks:
Have a look, the tool has removed country codes — the first 3 characters — from the phone numbers:
For instance, here's a list of clients with phone numbers and their countries in the same cells:
Depending on the country, I select cells by groups and set the tool to remove everything before US, UK, and then CA. This is what I get as a result:
After various modifications to your data, you may notice empty rows and columns scattered all over your sheet. To delete them, the first way that comes to mind is to select each row while pressing Ctrl and then remove those blank lines via the context menu. And repeat the same for columns.
Besides, you may want to eliminate those unused columns and rows that remain outside your data. After all, they take up space and advance exceeding the limit for 5 million cells in a spreadsheet.
What's even more, you may need to do the same in all sheets within the file.
Unlike Google Sheets, our add-on removes all empty and unused rows and columns in one go. You don't even need to select any range or individual columns and rows.
Just open your sheet, access the Clear tool, select 5 checkboxes (or less, depending on your goal), click Clear, and there you have your neat tables in all sheets without any gaps:
Another useful operation is splitting text from one column into several columns and from one row into several rows.
Tip. Feel free to check out some simple Google Sheets functions for simplest splitting tasks.
Though Google Sheets has recently introduced their own Split text to column feature, it has some major weak points:
Fortunately, our Split add-on deals with all of that for you. You will find the tool in the Split group in Power Tools:
First, I'd like to demonstrate how to split text by characters or delimiters within cells.
Tip. Watch this short demo video or feel free to read on :)
You should select the data to split first, make sure the option to Split by characters is selected, and choose those separators that occur in your cells.
I don't check Space since I don't want to pull apart names. However, Comma and Line break will help me separate phone numbers and job titles. A also choose to replace my original column with the result (the checkbox at the very bottom of the add-on):
Tip. If there are too many conjunctions or any other connective words, you can split text by them as well using the second option — Split values by strings.
If the text case matters the most, pick the third radio button and split everything before the capital letters.
As with adding text, the position of symbols in cells may be more significant than the occurrence of specific characters. Especially, if all cells are formatted in the same manner.
With the Split by position tool, you can choose a precise place at which records should be split:
I used this tool to separate country and area codes from the phone number itself:
All that remains now is to delete the original column and format those two new ones.
As I mentioned earlier, the standard tool of Google Sheets called Split text to columns merely drags words away from each other. If you use this tool for your names, there's a fair chance you'll get the columns where names, titles, and suffixes are mixed.
Our Split names tool will help you avoid that. It is intelligent enough to recognize first, last, and middle names; titles and salutations; post-nominals and suffixes. Thus, it doesn't just split the words. Depending on the name units, it puts them in the corresponding columns.
Moreover, you can pull, for example, only the first and last names no matter what other parts are there in cells. Watch this short video (1:45), the whole process takes literally just a few seconds:
If splitting all values in a cell is not an option and you'd rather extract a particular part from that Google Sheets cell, you may want to take a look at the Extract tool:
The first 4 are different ways to extract your data from Google Sheets cells:
You will also be able to get certain data types:
The following demo video shows the tool in action:
Voila! These are all instruments we have at the moment that will help you work with text in Google Sheets. They may become your lucky find, or simply save you time and nerves. In any way, I believe they are extremely useful to have.
And just a tiny reminder — you will find all these add-ons in Power Tools — a collection of all our utilities for Google Sheets.
If you have any questions or if your task is too intricate for these add-ons to serve you, just drop your comment below, and we'll see what we can do to help. :)
Table of contents