5 Ways to Change Text Case in Google Sheets

This post will explore all the different ways you can change text cases in Google Sheets.

The case is essential while working with text data in Google Sheets. If data has not been entered with the correct case, you will need a way to change it easily.

Thankfully, you don’t need to retype any data to change the text case. This can be done easily with various options found in Google Sheets.

Changing text case between the lower, upper and proper case is all possible in Google Sheets.

You must have come up with situations where you forcefully change the text case.

Throughout this article, you will learn the different ways of converting text between the various case styles. Get your copy of the workbook and follow along!

Change Text Case with Functions

The most popular and commonly used way of changing the text case within the Google Sheets is using functions.

You have three different functions that can do the task for you.

  • UPPER – This function converts text into upper case where each character is capitalized.
  • LOWER – This function converts text into lower case where each character is the lower case.
  • PROPER – This function converts text into proper case where first letter of every word is capitalized.

After using these to change the text case, you may want to convert the formula results to values.

Change Text Case with the UPPER Function

If you wish to convert your text or cells containing text into upper case, using the UPPER function is the best way.

The function takes a single text value or cell containing a text value as an argument and converts it into an upper-cased text.

Syntax for the UPPER Function

= UPPER ( text )
  • text – specifies a text string or a cell containing text that needs to be converted into upper case.

Example with the UPPER Function

Suppose you have a list of Job Titles as shown in the screenshot above, and you want to convert them into an upper case.

You can see that the cases used are mixed and do not look good. You can easily use the UPPER function to convert them. Follow the steps below.

= UPPER ( A2 )

To convert the text strings into upper case, copy and paste the above formula into cell B2. Cell A2 contains the text strings that you wish to convert into upper case, and it is used as the text argument in the UPPER function.

Drag this formula down across the cells to convert all Job Titles into upper case.

Change Text Case with the LOWER Function

Sometimes the situation demands you to convert the text into a lower case. You definitely don’t want to manually type this out again!

The Google Sheets LOWER function will help you get this task done.

The functionality is similar to UPPER and takes a single argument that consists of a text value or a cell containing a text value. It then returns those into a lower case.

Syntax for the LOWER Function

= LOWER ( text )
  • text – is a text string or a cell containing a text string that needs to be converted into lower case.

Example with the LOWER Function

= LOWER ( A2 )

Type the above formula into cell B2 of your sheet. Cell A2 contains a text value and the function will convert this value into lower case.

The text in cell A2 is now converted into lower case.

Copy and paste or drag the formula down across all the rows. You will see all Job Titles are now in lower case.

Change Text Case with the PROPER Function

If you look around, the ideal way to work with text data is to convert it into a proper case where each new word will be capitalized. A function named PROPER() does this task for you and makes your text appear in the proper case.

Syntax for the PROPER Function

= PROPER ( text )
  • text – specifies the text which needs to be in the proper case format where the first letter of each word is capitalized and the remaining letters are all lower case.

Example with the PROPER Function

= PROPER ( A2 )

Copy and paste the above formula in cell B2 of the spreadsheet. Cell A2 contains the text to change.

You can now see that the entire text in cell A2 is converted into a proper case. The first letter of each word is now capitalized.

You will need to drag this formula down across the rows to be able to apply proper casing to all the other cells as well.

Change Text Case to Sentence Case with a Formula

Another text case you might want to produce is the sentence case.

If you are working with paragraphs that consist of sentences delimited by a period, only the first word in each sentence is capitalized.

Unfortunately, there is no direct function that can do this task for you. However, you can use a combination of different functions that does this task and converts the text into a sentence case.

=JOIN(". ", ARRAYFORMULA(REPLACE(TRIM(SPLIT(A2, ".")), 1, 1, UPPER(LEFT(TRIM(SPLIT(A2, ".")),1)))))

Copy and paste the above formula to cell B2 to convert the text from column A to sentence case.

Column A contains the cells with sentences that are separated with the full stop or period character. The formula above converts the first letter of the first word on either side of the full stop into capitals to obtain the sentence case.

Explanation

  • The first thing you need to do is separate the sentences based on the full stop. The SPLIT does this based on the period.
  • The TRIM function gets applied to each sentence and removes any additional spaces.
  • The LEFT function will return the first character from each sentence.
  • The UPPER function then converts the first character to upper case.
  • The REPLACE function will replace the first characters with its capital version.
  • The split sentences are an array and as such need the ARRAYFORMULA function to operate with them.
  • You then need to combine all the sentences with the JOIN function separating them by a period.

That’s it! You can use this formula every time you have messy sentences where the first characters are not capitalized in sentence case.

Change Text Case with an Add-On

No matter how good the formula is, there is always an easier way. With an add-on, you can make this as easy as clicking a button.

With add-ons, you can gain extra functionality such as inserting random numbers in your Google Sheets.

Thankfully, Ablebits has developed a Change Case tool as part of its Power Tools. It allows you to change the text case into upper, lower, proper, a few more case types with just a few clicks.

To install the Power Tools add-on in your Google Sheets environment, follow these steps.

  1. Go to the Extensions menu.
  2. Click on the Add-ons option.
  3. Select the Get add-ons option from the submenu.

This will open up the Google Workspace Marketplace. It’s an app store with many add-ons that can be installed to your google workspace.

Once it is installed, you can access the Power Tools through the Extensions menu.

A new menu option named Power Tools will appear at the end of the list.

To use the Change Case tool, go to the Extensions menu, select Power Tools and choose Start from the submenu. This will launch the tool on the extreme right of the spreadsheet.

There are various tools available to work with. Make sure you have the range selected on your sheet where you want to make changes to the word case.

  1. Open the Change case tool. It can be found on the upper toolbar menu and has symbol Aa.
  2. You can then select various options to transform the case.

You can convert the text to sentence case, upper case, lower case, etc.

You even have the option to change the word into tOOGLE tEXT where characters that are in lower case are converted into upper case and vice versa. For example, the name Lalit Salunkhe will be converted into lALIT sALUNKHE.

For this example, you can select the case A1:A11. Then select the UPPERCASE option from Power Tools Change case dropdown to convert the text into the upper case from the selected range.

That’s it! No formulas, or anything complicated! It’s all done for you by this tool with a couple of clicks.

Change Text Case with Apps Scripts

Apps Script is an alternative and powerful way of writing scripts that can automate almost any task that you do in Google Sheets. You can use them to change the text case as well!

In the Apps Script environment, copy and paste the following code to change the text case into the upper case.

function changeCase() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Apps Script");
  for(var cell = 2; cell <=sheet.getLastRow(); cell++){
    var rng = sheet.getRange(cell,1);
    var val = rng.getValue();
    rng.setValue(val.toUpperCase());
  }
}

Explanation

  • Everything you develop in Apps Script is a function. Define a new function with name changeCase().
  • Once the function is defined, you need the standard spreadsheet methods to access the spreadsheet where the text to change case is stored. You use the SpreadsheetApp.getActiveSpreadsheet() clause to access the active spreadsheet. The getSheetByName() methods then captures the sheet with name Apps Script.
  • The next line creates a for loop starting from second row until the last working row in the sheet. On each itteration the row will be changed and system will move to the next active row.
  • The sheet.getRange() method returns the each cell that runs through for loop from first column until the last active cell. The results are stored into rng variable.
  • The val variable takes all the values from the selected range in the previous method. The rng.getValue() method does this task perfectly.
  • The toUpperCase() function is used inside the rng.setValue() method and allows you to set the values to upper case.

You can run this Apps Script by hitting the Run button in the script editor. It will start executing and once the execution is complete, the Job Titles will be all in caps as shown in the GIF above.

Change Text Case with Google Docs

This method is a bonus hack.

It is for those who don’t like formulae, can’t access the Power Tools, and don’t want to mess with Apps Script.

You need to open a new Google Docs. You can easily open a new one by typing docs.new in the Chrome address box.

Copy the data you wanted to change the text case of, and paste it in the newly opened doc file. You can use the standard Ctrl + C, Ctrl + V method to copy and paste your data.

Select the entire pasted data in Docs and then follow these steps.

  1. Go to the Format menu.
  2. Select Text from the options.
  3. Select Capitilization from the submenu.
  4. You can then select either lowercase, UPPERCASE, or Title Case from the submenu.

Click on any one of your choice and Google Docs will convert the selected text into that case.

Copy the data from your Google Doc and paste it back into Google Sheets.

Conclusion

Changing text cases is a common day-to-day scenario for Google Sheets users. There are various ways you can do this through Google Sheets.

You can use the standard functions such as UPPER, LOWER, PROPER to convert the text into upper case, lower case, and proper case respectively.

There is no specific function to convert the text into a sentence case. However, it’s possible to get this done with a complex formula.

The Power Tools add-on developed by Ablebits can do all the case examples mentioned plus a few others too, all with just a few clicks.

Apps Script code to change the text case can also be developed for any type of automation scenario needed.

Changing cases can even be done via Google Docs and the results are pasted back into Google Sheets.

Which of these methods to change the text case do you prefer? Do you know any others? Let me know in the comments below!

About the Author

Lalit Salunkhe

Lalit Salunkhe

Lalit is a data analyst with diverse skills and experience in data mining and analysis. He has a M.Sc. in Statistics from one of the top institutes in Maharashtra. Lalit is also a Google Sheets expert and enjoys teaching others how to use Google Sheets to solve their data problems.

Related Articles

Comments

0 Comments

Submit a Comment

Your email address will not be published.

Get the Latest Google Sheets Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in Google Sheets!