by Natalia Sharashova, updated on
Changing text case in Google Sheets may seem an intricate task as there is no such option in the standard menu. Even so, the task is very much resolvable. In this blog post, I share different ways to capitalize your words or turn them into lowercase, uppercase, and sentence case.
When you don't find an easy way to change the case in Google Sheets, the first alternative that may come to mind is Google Docs — the service where this option exists in the menu. But since you're in Sheets, you'll need to get your data to Docs first:
If you ask me, this way may do if you have a small dataset and some extra minutes to shift records back and forth. But if neither is about you, it's better to use one of the alternatives Google Sheets offers to change the text case right there.
Functions are the only standard way to change case in Google Sheets. Be ready to prepare extra column(s) where you will have to enter formulas and see the result.
Tip. If you don't like functions or have no time or desire to learn them, jump right to the part where I introduce you to a special tool. With it, you'll just click the required option and it will do the rest for you.
The PROPER function is used in Google Sheets to capitalize the first letter of all words in a cell:
It requires only one argument: a text where you want to change the case or a reference to a cell with that text.
Since my data is in the column, I will reference its cells using the Google Sheets PROPER function in the neighbouring empty column:
Once I enter the formula, Google Sheets offers to copy it down for me and capitalize the first letters in all cells:
You can either press Ctrl+Enter or click the tick icon to allow spreadsheets to do that.
Tip. If that doesn't happen in your file, you can copy the formula down the column manually using one of the ways described here.
And here you go, the entire column in Google Sheets where you capitalized the first letter in each word:
You can go further and build an array formula that will capitalize each word in the entire column at once:
Tip. To get rid of the formulas eventually and turn records into editable values, follow the instructions from this blog post.
The next one to change case is the UPPER function in Google Sheets. As you may have guessed, it capitalizes every single character in a cell. The entire text will be written in upper case.
It also requires only one argument — the text to convert to uppercase:
I'm going to enter it in B2 while referencing A2 and then copy it down the column as well:
To make the text in Google Sheets uppercase in the entire column with one formula right away, the same ArrayFormula will help:
Tip. And again, to convert formulas to values, use one of the ways described in this blog post.
Last but not least, it is the LOWER function. Yes, it turns all text into lowercase :)
Like the 2 aforementioned functions, this one also needs just 1 thing: text or a reference to a cell with the text:
Let's change case in the same Google Sheets uppercase column from the example above. This time, I will turn it to lowercase:
And even if you know it already, I will still show you how it looks when the ArrayFormula automatically populates all cells in a range you specify with the result:
Tip. If necessary, turn formulas into values as shown in this blog post.
Sentence case is yet another way to present your text. If you want your cells to appear as sentences, it means that you want Google Sheets to make only the first letter of each cell capital without changing case of other letters.
Sadly, there's no special function in spreadsheets as there is for the proper text case, for example.
Even so, there is a combination of other functions for Google Sheets that produce a formula that eventually capitalizes only the first letter in cells. I'm not going to lie — it's not the easiest one and it is definitely a bit of a learning curve.
Let me show you the formula first, and then I'll explain how it works:
=JOIN(". ",ArrayFormula (REPLACE(TRIM(SPLIT(A2,".")),1,1,UPPER(LEFT(TRIM(SPLIT(A2,".")),1)))))
I duplicated the contents in some cells (so there are sentences) and highlighted them grey so you could better understand what's happening before the case is changed (see cells A2, A6, A9 in the screenshot above):
If this formula still makes you procrastinate — I feel you. The last method I'm going to share with you is much easier. It's actually my favorite since in order to change the text case I only need to select the range and press two buttons. Come take a look.
No matter the formula you use, all of them — plain and complicated — require an extra column to reside. And if later you turn those formulas into editable values, it will take even more time and effort.
Luckily, we have the simplest formula-free solution to change the text case in Google Sheets: Power Tools add-on. It's a collection of 30+ utilities for spreadsheets that includes the Change case tool.
Changing case in Google Sheets with this extensions is as easy as pie:
See for yourself, I will change the case in 6 different ways in a few seconds:
Note. When you choose to apply Sentence case, it will only capitalize the first letter of each cell/sentence. It will not lower other letters in order not to corrupt any abbreviations or names you may have. If you want to lower all other letters anyway, you will need to apply lowercase first.
In addition to 4 cases that can be covered with formulas — Sentence case, Capitalize Each Word (proper case), upper and lower case — we also make it possible to tOGGLE tEXT. It means simultaneously changing all letters in uppercase to lowercase and vice versa.
Tip. Capitalize Each Word also doesn't lower other letters in order not to corrupt possible names and abbreviations. To lower everything anyway, apply lower & Capitalize instead.
The add-on even features one extra setting — Ignore formulas. It skips cells with formulas (for all 6 cases) in order not to turn them into values.
If you choose the add-on — there are no extra columns, no formulas, and no need to convert formulas into values. The process is as easy and straightforward as possible.
This video demonstrates how easy it is to use the Change case add-on. I invite you to watch it to get to know the tool better.
You can install your Power Tools from the Google Store and try changing the text case in Google Sheets on your own. These instructions will help you out as well.
Whatever way you choose as your go-to, if you have any questions about it, please ask them in the comments section below.
Table of contents