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 fulfill three main tasks in a spreadsheet: add text to your values, change case, and replace special characters.

Before you start

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

How to change case, add or remove characters in Google Sheets

Run the Text tool

  1. Go to Add-ons > Power Tools > Start in the Google Sheets menu:
    Run Power Tools.
  2. Click on the Text icon on the add-on's sidebar:
    Click the Text icon on the Power Tools sidebar.
  3. Select the range with your text and decide what to do with it.
    Tip. You can pick Add-ons > Power Tools > Text on the first step to see the tool's options right away.

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 while lowering letters in other words.

    Note. If other words start from capital letters or are written in all caps, the add-on won't lower them in order not to corrupt possible names and abbreviations. To lower such letters 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. To avoid corrupting any acronyms you may have, check the box to Ignore abbreviations.
  • Lower case

    Convert all values of interest to lowercase by picking this radio button.

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

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

Add text by position in a spreadsheet

This group lets you add the desired characters to any position in the selected cells with text. Add an area code or an extension to the telephone numbers, extend the street name, you name it:
Add text by position in Google Sheets.

  1. Enter the substring you would like to add in the first field.
  2. Select the position for the new text in the selected cells:
    • Pick At the beginning to begin each cell with the entered text.
    • Select At the end to append the necessary text to each cell.
    • Choose After character number and specify the exact position for your text in the selected cell. You can either enter or pick the character number using the up and down arrows.
    • If the position varies from cell to cell, and it depends on certain values, use the Before text option and enter the value that has to follow your new text.
    • Use the After text option to insert new text after the entered value. Type the entry to search for in the range into the text field.

Press Run and see the necessary text added to the specified position.

Replace symbols

Use this tab 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.

After you choose what to replace, click Run 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 Run. The text in the selected range will be polished in no time.

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.