July 21, 2021

Change Text Case in Google Sheets

Google Sheet spreadsheet

UPPER, LOWER, and PROPER

For better presentation, the text in your Google Sheets may need to be in upper case, lower case, or have its first letter capitalized. We have dedicated functions for that:

UPPER: converts all letters to uppercase

UPPER formula applied to different possible inputs
UPPER formula applied to different possible inputs

LOWER: converts all letters to lowercase

LOWER formula applied to different possible inputs
LOWER formula applied to different possible inputs

PROPER: converts the first letter of a word to uppercase and all the other letters to lowercase, turning the input into a properly-capitalized word.

PROPER formula applied to different possible inputs
PROPER formula applied to different possible inputs

The function applies to all words in the cell:

PROPER formula applied to different possible inputs
PROPER formula applied to different possible inputs

If you applied the formula to cells with only numbers instead, no error will be produced:

Numbers are ignored by the three functions.
Numbers are ignored by the three functions

It also ignores the numbers if the cell contains both numbers and letters:


Numbers are ignored, even if they form a string with no spacing between the letters
Numbers are ignored, even if they form a string with no spacing between the letters

Properly format a sentence

If you need to properly format a sentence using sentence case, where only the first word of the sentence is capitalized, here is one quick trick:

=concatenate(upper(left(string_cell)),lower(right(string_cell,len(string_cell)-1)))

Where string_cell refers to the cell containing the string to be formatted. You can see it in action below:

The first alternative formula applied to different strings

As you can see, the formula capitalizes the first letter while changing all the other letters to lowercase. This is useful for most scenarios, but if there are proper names within the sentence, their first letters are also changed to lowercase. 

If you already have a well-formatted input which only needs to have its first letter capitalized, here is an alternative code:

=replace(string_cell,1,1,upper(left(string_cell)))

Where string_cell refers to the cell containing the string to be formatted. You can see this formula in action below:



The second alternative formula applied to different strings
The second alternative formula applied to different strings

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.