How to work with text in Google Sheets

Power Tools provides you with various utilities to speed up most of your daily tasks. Using this tool, you can fulfil a few main tasks in a spreadsheet: change case, replace special characters, and polish text.

Before you start

The Text tool cannot process dates. Cells formatted as dates will be ignored to avoid data corruption.

How to change case and replace characters in Google Sheets

Run the Text tool

  1. Go to Extensions > Power Tools > Start in the Google Sheets menu:
    Run Power Tools.
  2. Enter the Text group:
    Enter the Text group in Power Tools.
  3. Click the Modify icon on the add-on sidebar:
    Click the Modify icon to access the tool.
  4. Select the range with your text and decide what to do with it.

Change case in Google Sheets

Open the Change case group to modify the text capitalization in the selected cells:
Change case of the text in Google Sheets.

  • Sentence case.
    Make each sentence in all cells of the range start with a capital letter.

    Note. The option doesn't lower other letters in cells in order not to corrupt possible names and abbreviations. To lower everything anyway, please apply the lower case option first.
  • Capitalize Each Word
    Use this option whenever you want to make the first letter of every word in the selected cells capital.
    Tip. This setting doesn't lower other letters in cells in order not to corrupt possible names and abbreviations. To lower everything anyway, apply lower & Capitalize instead.
  • lower case
    Convert all values of interest to lowercase by picking this radio button.
  • lower & Capitalize
    For when you need not only to capitalize each word in a cell but also lower all other letters.
    Tip. To keep other letters intact, use Capitalize Each Word instead.
  • UPPER CASE
    Use this option to transform all the words in the selected cells to uppercase.
  • tOGGLE tEXT
    Reverse the case of each letter in the selected range.
Tip. The last checkbox, Ignore formulas, makes the tool skip cells with formulas that your range may contain. Otherwise, they will be processed as well and replaced with values.

Once the option is chosen, click Modify and the case will be changed.

Replace symbols

Use this accordion to get rid of all special characters without breaking your data. It will help you make all characters in the range of interest uniform:
Replace specific symbols within the range in Google Sheets.

  • When you import data with foreign text, select Replace accented characters to remove diacritical marks from the letters, e.g. replace all instances of á with a.
  • Choose the option to Replace codes with symbols to get all the copyright (©), ampersand (&), and all similar signs instead of their codes.

    E.g., you import book titles from the web, and some of them contain codes like "War & Piece". Use this tool to change those codes into their corresponding symbols — "War & Piece".

  • Select the Replace symbols with codes radio button to remove all the copyright (©), ampersand (&), and all similar signs and paste their codes instead.
  • Other characters you may need to change are smart quotes (curved quotation marks). The last setting will replace them with straight quotes in all selected cells.

After you choose what to replace, click Modify and see the changes in the range of your choice.

Polish text

This group contains the most commonly used options to bring all text in your spreadsheet back to its normal readable form:
Normalize text in Google Sheets.

  • To make sure no excess space has delved into your text, tick off Remove extra spaces.
  • On the contrary, we may lack some spaces after commas, full stops, brackets, etc. Choose Add space after punctuation marks to return them to their place.
  • Make the text in each cell begin with the capital letter while lowering others with the Sentence case option.

Check some or all boxes depending on what you need to normalize and press Modify. The text in the selected range will be polished in no time.

Responses

Sheli Crabtree says:
October 8, 2019 at 10:47 am

Is there a way to reverse name placement? For example,
Sargent,Kristin
I need it to look like Kristin Sargent

Thank you for your question, Sheli.

A couple of our add-ons will help you (both of them can be found in Power Tools):

  1. First, use Split Names to divide names to separate columns. You will have one column with the initial "Sargent,Kristin", then one column with first names - "Kristin", and one more column with last names - "Sargent".
  2. Then combine those new columns using Merge Values and separate the name units with a space. You will have "Kristin Sargent" as a result.

Please let us know if you have any other questions.

Marja Arkonkoski says:
September 25, 2019 at 8:01 am

Hi! Is there a way to customize the Replace symbols option? I wish to change ä to ä. Thanks!

Jimmy Pappadeas says:
September 13, 2019 at 2:28 pm

The text functions don't seem to be working for me. I have tried to use the change case (Capitalize each word) to clean up some survey data that I had, but after highlighting the cells and selecting "Run" nothing happened.

I am in a trial period and used a few of the deduplication functions successfully, but I don't know if I can decide on the value if some of the featured tools aren't working as expected.

Ehud Kirsh says:
June 7, 2019 at 6:21 pm

Is it possible to make it so that each time I write, each word will be capitalised by default? (as opposed to me having to select that cell and manually capitalise it with Power Tools).

Also, is it possible to make the text always centred by default? Sometimes when new cells are inserted/old cells are deleted/merged/unmerged, the text is aligned to the right.

Thank you for your interest in our product, Ehud.

I'm afraid it's technically impossible to keep our add-on running on the background applying the cases right away. You need to run it each time you need to capitalize cells. A quicker way to start the operation is from the Recent tools tab at the bottom of Power Tools.

As for centring, you need to select all new cells and apply the necessary alignment using the Google Sheets standard utility. Alternatively, you can use their Paint format tool to copy the formatting from one cell to other cells.

Hatem Saeed Hinawi says:
May 16, 2019 at 9:59 pm

Hi,
Which option I can use to find duplicate numbers?
And how to remove them automatically?

Hi Hatem,
Thank you for contacting us.

If we understand your task correctly, our Remove Duplicates may be helpful. The add-ons allow you to remove duplicate rows, cells and compare 2 tables for duplicates. Just run Power Tools, go to the Dedupe and Compare section and choose the necessary add-on.

Currently, it's impossible to run our add-ons automatically. You need to run it each time you want to remove duplicates. However, our Remove Duplicate Rows contains scenarios that let you save the options you select most often and run them in a click. Depending on your task, you may find this feature helpful.

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

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.