by 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.
Format text in Google Docs
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:
- Select your records in spreadsheets:
- You can select the required data range manually.
- Select all records from one column using Ctrl+Shift+down arrow
- Or select the entire used range with Ctrl+A
- Once records are selected, copy them by pressing Ctrl+C
- Open the new Google Doc and press Ctrl+V to paste the copied data there.
- Then select it once again, go to Format > Text > Capitalization, and there you'll see 3 ways Google Docs changes cases: lowercase, UPPERCASE, Title Case:
- Click whatever option is necessary to change your text case, and then just copy-paste the data back to Google Sheets:
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.
Google Sheets functions to change case
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.
PROPER function — capitalize all words
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:
=PROPER(A2)
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:
=ArrayFormula(PROPER(A2:A9))
Tip. To get rid of the formulas eventually and turn records into editable values, follow the instructions from this blog post.
UPPER function
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:
=UPPER(A2)
To make the text in Google Sheets uppercase in the entire column with one formula right away, the same ArrayFormula will help:
=ArrayFormula(UPPER(A2:A9))
Tip. And again, to convert formulas to values, use one of the ways described in this blog post.
LOWER function
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:
=LOWER(A2)
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:
=ArrayFormula(LOWER(A2:A9))
Tip. If necessary, turn formulas into values as shown in this blog post.
Apply Sentence case using functions
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 there are several sentences in a cell, SPLIT(A2,"." ) divides them into separate cells by a period.
- Then TRIM enfolds those sentences and removes all excess spaces.
- The part at the end of the formula — UPPER(LEFT(TRIM(SPLIT(A2,"." )),1)) — not just splits the contents but also extracts and capitalizes only the first letter (the LEFTmost one) of each sentence.
- REPLACE — well — replaces the first lowercase letters of all sentences with their uppercase equivalents.
- When sentences are split, Google Sheets sees them as an array of cells. To make sure all split sentences are processed, I wrap everything in ArrayFormula.
- And finally, JOIN collects those individual sentences back into their cells.
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.
Change case add-on for Google Sheets
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:
- Select the range.
- Choose the required text case:
- Press Run.
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.
Video: how to change case in Google Sheets
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.
10 comments
Your formula for sentence case as presented only works if the original is all lower case. The problem I was trying to solve was changing input in all UPPER CASE to sentence case. THankfully, your formula was very nearly there. I added a lower() to the first pull of the origination cell and it became perfect:
=JOIN(". ",ArrayFormula (REPLACE(TRIM(SPLIT(LOWER(A2),".")),1,1,UPPER(LEFT(TRIM(SPLIT(A2,".")),1)))))
Now, if A2 has:
THIS IS AN ALL CAPS SENTENCE. AND SO IS THIS.
It will return:
This is an all caps sentence. And so is this.
I imagine the reason you left out the lower() was to allow it to respect any internal proper names in values like "i asked Dr. Smith his wife's name. he said it is Marla." But these instances would be far more rare than the whole thing being all upper or all lower, so I think putting the lower() function in there makes it more generally helpful.
Appreciate your input, Marshall! I'll update the article one of these days with your finding :)
Excellent post. I have been trying to solve this challenge for two days, and I was able to solve the issues in a few minutes with the first option. Thank you so much
Appreciate your feedback, Dili, I'm glad the article helped!
This was super helpful and thorough, I love the providing of various options and the clarity of when and why I'd use those different methods. A+!
Thank you so much for your kind words, Jack :)
Hello-
My Google Sheets formatting options do not look like your instructions. I am missing the Capitalization option among others. Any idea why?
Hello Bill,
Those options are from Google Docs, not Google Sheets. Please pay attention to the first section of the article: it's about formatting text in Google Docs and bringing it back to Google Sheets :)
Hi, what is the formula to ignore abbreviated names of persons? Example: I have a person’s name that is SJW, and the function I use right now is the PROPER (which turns SJW into Sjw). I wanted to use your add-on, but I would like something that does it in real-time rather than having to intervene each time.
Hi LeAnneaux,
To use formulas, you will have to create a list of all possible abbreviations there can be in your file and then reference that list in the SUBSTITUTE formula like this (B1 is a reference cell):
=SUBSTITUTE(PROPER(A1),PROPER(B1),B1)