Change Text Case in Google Sheets: Upper, Lower, Proper, Sentence

18

Google Sheets has built-in functions to change text case to upper, lower, and proper case. However, there is no built-in function for sentence cases.

In this article, we will show you how to use the built-in functions and a custom function to change text case to upper, lower, proper, and sentence case in Google Sheets.

What is each text case?

  • Upper case: All letters in the text are converted to uppercase letters.
  • Lower case: All letters in the text are converted to lowercase letters.
  • Proper case: The first letter of each word is converted to uppercase letters and all other letters in lowercase.
  • Sentence case: The first letter of each sentence is converted to uppercase letters.

How to Change Text to Upper, Lower, Proper Case in Google Sheets

Since we are using built-in functions, we can convert text in a cell using a formula in another cell. For example, if the text to convert is in cell C6, you can convert it in cell D6 using the following formula:

=UPPER(C6)

This will convert the text in cell C6 to uppercase and return the result in cell D6.

But we can also replace the existing text with the converted one in the same cell. To do this, copy the formula and paste it into the same cell where the original text is.

However, instead of just pasting the formula, right-click on the cell and select Paste Special > Paste Values only. This will paste the converted text into the cell, but not the formula.

Here are the steps in detail as per the above example:

  1. Copy the formula result in cell D6.
  2. Right-click on cell C6 and select Paste Special > Paste Values only.
  3. Delete the formula in cell D6.

This is a useful trick to know if you want to change the text case in a cell without having to use an additional column.

Upper Case

To change the text case to upper in Google Sheets, we can use the UPPER function. This function converts all the characters (alphabets) in a text to capital letters.

Syntax:

UPPER(text)

Argument:

text: The string (text) to convert to upper case.

Example:

The following formula in cell B1 will convert the text in cell A1 to upper case:

=UPPER(A1)

If there is a value in cell A1, it will be converted. If there is no value in cell A1, the formula will return an empty string.

How to change text to upper case in Google Sheets

How to Change Text Case to Upper in a Column in Google Sheets?

To change the text case to the upper in a column in Google Sheets, we can use the UPPER function with the ARRAYFORMULA function.

Make sure that the column where you want to change the text case is empty. Otherwise, you will see #REF! error.

In the first cell in the empty column, enter the following formula:

=ARRAYFORMULA(UPPER(A1:A))

Replace A1:A with the range of cells that contain the text that you want to convert.

Press Enter.

The formula will be applied to all of the cells in the column, and the text case will be changed to upper.

Lower Case

The LOWER function in Google Sheets converts the text in any case to lowercase. It converts all the characters (alphabets) in a text into small case letters.

Syntax:

LOWER(text)

Argument:

text: The string (text) to convert to lower case.

Example:

The following formula in cell B1 will convert the text in cell A1 to lowercase:

=LOWER(A1)

If there is a value in cell A1, it will be converted. If there is no value in cell A1, the formula will return an empty string.

How to change text to lower case in Google Sheets

Example to LOWER case array formula in Google Sheets:

The strings to convert to lowercase are in the array/range A1:A. The following array formula can be entered in cell B1 to convert all of the text in column A to lowercase:

=ARRAYFORMULA(LOWER(A1:A))

Proper Case

To change the text to the proper case in Google Sheets, we can use the PROPER function. The PROPER function capitalizes the first letter of each word, and all other letters are converted to lowercase.

Syntax:

PROPER(text)

Argument:

text: The string (text) to convert to proper case.

Example:

The following formula in cell B1 will convert the text in cell A1 to proper case:

=PROPER(A1)

If there is a value in cell A1, it will be converted. If there is no value in cell A1, the formula will return an empty string.

To convert all the texts in a column (here column A) to the proper case, we can use the following array formula:

=ARRAYFORMULA(PROPER(A1:A))
How to change text to proper case in Google Sheets

How to Change Text to Sentence Case in Google Sheets

If you want to change text to sentence case in Google Sheets, you must use a custom formula or custom function, as there is no built-in function for this.

Unlike proper cases, in sentence cases, only the first letter of each sentence is capitalized. All other letters remain the same.

Here is the sample text in cell A1:

this is a sentence. this is another sentence: this is a third sentence? please correct ME

Expected Result:

This is a sentence. This is another sentence: This is a third sentence? Please correct ME

The following formula will change the text in cell A1 to sentence case:

=ARRAYFORMULA(LET(
   textA, A1,
   testA, TOCOL(SPLIT(textA,". ",FALSE),1),
   textB, TEXTJOIN(". ",TRUE,REPLACE(testA,1,1,UPPER(LEFT(testA,1)))),
   testB, TOCOL(SPLIT(textB,": ",FALSE),1),
   textC, TEXTJOIN(": ",TRUE,REPLACE(testB,1,1,UPPER(LEFT(testB,1)))),
   testC, TOCOL(SPLIT(textC,"? ",FALSE),1),
   IFERROR(TEXTJOIN("? ",TRUE,REPLACE(testC,1,1,UPPER(LEFT(testC,1)))),
)))

It splits the text in cell A1 into sentences using the period, colon, and question mark delimiters, capitalizes the first letter of each sentence using the UPPER function, and then rejoins the sentences into a single string using the same delimiters again.

TOSENTENCECASE Named Function

You have seen examples of using the built-in functions for changing text to upper, lower, and proper case in Google Sheets. In the same way, you can use the TOSENTENCECASE named function in Google Sheets to convert text to sentence case.

First, you need to import the TOSENTENCECASE function from the source sheet below:

TOSENTENCECASE

Syntax:

TOSENTENCECASE(text)

Argument:

text: The string (text) to convert to sentence case.

To convert the text in cell A1 to sentence case, use the following formula:

=TOSENTENCECASE(A1)

Unlike the built-in functions, you do not need to use the ARRAYFORMULA function to convert multiple cells to sentence cases. You can use the MAP function with a lambda function instead:

=MAP(A1:A,LAMBDA(row,TOSENTENCECASE(row)))

In this formula, replace A1:A with a range of values that you want to convert to sentence case.

Prashanth KV
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

18 COMMENTS

    • Hi, Nicole,

      Of course, you can use ArrayFormula with all those functions except the custom ‘Sentence Case’ formula. I’ve updated the post to include the array formulas.

      • I don’t believe you have interpreted the question correctly, as I believe I am looking for the same thing. I want the column I type information into, to automatically capitalize. Not using a second column and then copy/pasting, this needs to be done programmatically, as the example here provides: “https://productforums.google.com/forum/#!topic/docs/QaRXTzt6lbw”

        The trouble is, this also capitalizes formulas in your cells, which is breaking the importhtml() function as it is capitalizing the URLs used.

        How can Google Sheet automatically capitalize specific columns of data, but not the formulas inside the cells?

    • Hi,
      Go to tools>Script Editor
      Then replace the content with the following script.
      function onEdit(e) {
      if (typeof e.value != 'object') {
      e.range.setValue(e.value.toUpperCase());
      }
      }

      Save it.
      Done! Open a new Google sheet and type any text. See the text automatically get converted to UPPER!

      • hi,
        that script worked great. how do you make it only apply to a range of cells, rows, or columns….instead of entire sheet.
        thanks for the info

      • Hi Prashant,

        I am trying to format a column in google sheets where I have initials of the people. I want all the initials to appear in this manner:
        “Prashant V.”
        however, some people may write it either in lower case like this:
        “prashant v.”
        or may not use a full stop in the end. is there a way to do this via conditional formatting.

        • Hi, Lubna,

          Why don’t you use the PROPER function?

          =proper(A1)

          In that, the name is in cell A1. You can’t use conditional formatting for that.

          The above is with a name having a first name and a single initial letter like “prashant v.” or “prashant v”

          If the name is having a first name and two initial letters like “john kv.” or “john kv”, then you can try this formula.

          =PROPER(index(split(A1," "),1))&" "&upper(index(split(A1," "),2))

          Best,

LEAVE A REPLY

Please enter your comment!
Please enter your name here