Easy Way to All Cap in Google Shet

Let's learn how to change text case to upper, lower, proper, or sentence case in Google Sheets.

As you may already know, I have a separate section on Info Inspired that completely dedicated to Google Doc Spreadsheet Tutorials and Excel Vs Sheets formulas.

This post is written keeping in mind both Excel and Google Sheets users except at one point. I have a custom formula for sentence case, that formula use is not applicable in Excel.

Many users know how to change the text case in Document Editors but not in Spreadsheets. I hope this post will be helpful to them.

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

Open a spreadsheet and just read and apply the points (formulas) under the subtitles given below.

You can first apply the formulas on any spreadsheet cell to get the result. It will change the text form (text case), but you will get the result in a new cell.

For example, the text in cell C6 can be converted in cell D6 or any other cell (see the screenshot) not in cell C6 itself. It is because we are using functions to convert text case in Google Sheets.

But don't worry! We can later replace the existing text with the converted one. I mean, you can apply the upper, lower, proper, or sentence case in the same cell where the original text is.

To learn that jump to the last paragraph (Conclusion part) of this tutorial.

How to Change Text to Upper Case in Google Sheets

For this, we can use the UPPER() function in Google Sheets. It 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 to the UPPER function in Google Sheets:

In this example, I am going to convert the text in cell A1 to upper case using the formula =upper(A1)in cell B1.

By following this method you can change Text to Upper Case in Google Sheets.

Assume there are texts in A1, A2, A3, and so on. I mean a whole column range. Then we can use the Upper function in Google Sheets with ArrayFormula.

First, make empty the range B1:B and then insert the below array formula in cell B1.

          =ArrayFormula(upper(A1:A))        

How to Change Text to Lower Case in Google Sheets

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

Syntax:

          =lower(text)        

Arguments:

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

Example to the use of LOWER function in Google Sheets:

          =lower(A1)        

Example to LOWER case array formula in Google Sheets:

The strings to convert to lowercase are in the array/range A1:A. Given below is the array formula to key in cell B1.

          =ArrayFormula(LOWER(A1:A))        

How to Change Text to Proper Case in Google Sheets

Here also we can depend on a dedicated function called PROPER() in Google Sheets.

Syntax:

          =proper(text)        

Argument:

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

The PROPER function in Google Sheets formats the first letter of each word into uppercase and all the other letters into lowercase.

Example:

          =proper(A1)        

To convert all the texts in a column (here column A) to proper case, then use the below Array Formula.

          =ArrayFormula(proper(A1:A))        

Sentence Case in Google Sheets

If you want a sentence case in Google Sheets, not the proper case as above, you must depend on a custom formula as there is no built-in function to do this in Google Sheets.

Unlike PROPER, in sentence case in Google Sheets, the first letter of each sentence will be in capital letters. All the other letters will retain its original case (the formula actually converts the first letter after any period to upper case letter).

This formula won't work in Excel and may not be able to use as an array formula.

          =ArrayFormula(join(". ",replace(transpose(TRIM(SPLIT( A1 , "." ))),1,1,  upper(left(transpose(TRIM(SPLIT( A1 , "." ))),1))))&".")        

In this sentence case formula, please replace cell A1 with the cell that contains your text.

To know how I have coded this formula, here is the tutorial – How to Capitalise the First Letter of a Sentence in Google Sheets.

Conclusion

Of course, the above formulas changed the upper case to lower case or lower case to upper case likewise. But the result is in a new cell.

You want to change the case of letters in the same cell where the text is. Right?

To do that copy the formula and paste it in the same cell where the original text is. But don't just paste!

You must right-click on the cell and go to Paste Special > Paste Values only. After that, you can erase the formula.

Resources:

• Bulk Change Case in Google Sheets Using Query.
• Formula to Filter Uppercase | Lowercase | Proper Case Text in Google Sheets.
• Restrict or Force Text Entry to All Caps, All Lower Case, or Proper Case.
• How to Restrict Entering Special Characters in Google Sheets Using Regex.
• Extract Vowels and Consonants Separately in Google Sheets.

brownslis1989.blogspot.com

Source: https://infoinspired.com/google-docs/spreadsheet/google-doc-spreadsheet-change-text-to-upper-lower-and-sentence-case/

Share this post