Handy tools to manage text in Google Sheets

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. ;)

Modify text in your spreadsheets

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 rights 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.

Change case in Google Sheets

Standard ways of changing case 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 case in your Google Sheets in original cells themselves.

You will find the tool in the the Text group > Modify:

Tools to manage text in Power Tools.

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), 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 Run and watch your original data change the case:

Change case in Google Sheets without formulas and extra columns.

Replace symbols

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 (&), 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.

Remove diacritical marks from letters.

Here's also what you can do with codes and special characters using the same add-on:

Replace codes with symbols and back.
And here you can see how the Replace smart quotes with straight quotes tool works (currently for double-quotes only):

Replace smart quotes with straight ones.

Polish text

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:

  • removes white spaces if there are any
  • adds space after punctuation marks if you forgot any
  • applies sentence case to your cells

You are free to go with all three options at once or pick the one that suits your table best:

A swift way to polish your Google Sheets text.

How to add text in Google Sheets

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.

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.

Insert special chars at the beginning / at the end

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 Run:

Add numbers at the beginning of Google Sheets cells.

Add text in Google Sheets before text / after text

The last three options of the tool let you insert characters depending on specific text in cells.

  • You can add your text starting from the 3rd, 7th, 10th, etc. character in a cell with the option called After character number. I'm going to use this tool and insert area codes wrapped in brackets to the numbers from the previous example.

    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:

    Add an opening bracket after the first character.

    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:

    Insert special characters in Google Sheets.

  • You can also add text before or after specific text in cells.

    These options will help me make the phone numbers even more readable by adding spaces before and after the brackets:

    Insert chars in Google Sheets before and after the specific text.

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.

Remove excess and special characters in Google Sheets

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:

The Remove group in Power Tools.

Tip. The Remove group also owns a help page where all the tools and their options are mentioned.

Remove substrings or individual characters

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:

  • all occurrences of one specific letter, number, or Google Sheets special character, e.g. 1 or +
  • multiple single letters, numbers, or characters: e.g. 1 and +
  • a specified sequence of characters — Google Sheets substring — or a few of such sets, e.g. +1 and/or +44

I'll take the same phone numbers from the previous example and remove all country codes and brackets at once with the tool:

How to remove Google Sheets substrings and special characters.

Remove spaces and delimiters

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:

How to remove spaces in Google Sheets.

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:

How to remove special characters from Google Sheets.

Remove chars by position

Sometimes though it is not the characters themselves that matter but their position in cells.

  • In my example, there are extensions in phone numbers that take the same place — from 12th to 14th character in each cell.

    I will use this position to remove the extensions from all numbers with the corresponding tool:

    Remove characters by position.

    Here's how the numbers transform in just a couple of clicks:

    Eliminate extensions from the phone numbers by their position.

  • You can clean up some amount of the first/last characters in cells in the same fashion. Just specify the exact number of extra symbols and the add-on won't keep you waiting.

    Have a look, the tool has removed country codes — the first 3 characters — from the phone numbers:

    Remove several characters from the beginning of all cells.

  • If multiple cells contain the same text preceded or followed by unnecessary details, use the option Remove characters before/after text to cast them out.

    For instance, here's a list of clients with phone numbers and their countries in the same cells:

    The client list before removing any data.

    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:

    Delete everything before the specific text.

Remove empty rows and columns in Google Sheets

After various modifications with 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 get rid of those unused columns and rows that remain outside of 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:

How to quickly remove empty rows and columns in Google Sheets.

How to split text to columns

The last but not least operation for this blog post is splitting text from one column into several columns.

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:

  • It splits by one delimiter at a time. If there are different delimiters in your cells, you'll have to use the utility several times.
  • It doesn't separate by line breaks. It let you specify custom separators, but entering the line break there can become a problem.
  • It overwrites data to the right when splitting cells from columns to the left of your table.
  • When splitting names, it doesn't recognize first, last, and middle ones — it simply splits the words.

Fortunately, our Split add-on deals with all of that for you. You will find the tool in the Split group in Power Tools:

The Split group in Power Tools.

Tip. As usual, there's a help page with the descriptions of all options, make sure to check it out as well.

Split by character

First, I'd like to demonstrate how to split text by characters or delimiters within cells.

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):

Split text to columns by characters in Google Sheets.

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.

Split by position

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:

Specify the position to split cells in Google Sheets.

I used this tool to separate country and area codes from the phone number itself:

Split text to columns by position in Google Sheets.

All that remains now is to delete the original column and format those two new ones.

Split names

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 not just splits the words. Depending on the name units, it puts them to the corresponding columns.

Moreover, you can pull, for example, only the first and last names no matter what other parts are there in cells. Take a look below, the whole process just takes a few seconds:

Pull first and last names from cells with full names.

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 small reminder — all these add-ons can be found 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. :)

2 responses to "Handy tools to manage text in Google Sheets"

  1. John says:

    I have not been able to identify a character in my file with imported data. Thankfully it sits by itself in the cell so that it does not gum up the works on cells with numbers or texts since there is no indication about what it is. The character behaves similar to a non-breaking space. However, the CODE (as does the UNICODE) function produces a "#VALUE!" error. Entering the cell, the cursor does not move over it, it truly appears blank.

    How do I know it is there? When I perform an "end" down arrow, the cursor moves to the end of the column, bypassing cells with text.

    The "COUNT" function returns 0 (this function sees no values). The "COUNTBLANK" function counts the cell (this function thinks the cell is blank). The "LEN" function returns 0.

    The only method of 'fixing' it is to delete the cell. It is blank anyway. The Filter function sees these offending cells as "(Blanks)". I have sorted based on blanks and deleted the cells. Then everything works as expected. I would much rather find all occurrences of this character and delete them.

    When pasting into a WORD file, I can make the character visible. The ASCII code for this in WORD is "00A0" which is 160 in decimal! However, copying it back to EXCEL does not change my original result.

    Unfortunately, I am unable to attach a screen cap or a file with the issue.

    Please contact me if this peaks your interest...

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)