How to Capitalize the First Letter in Google Sheets

puneet-gogia-excel-champs

- Written by Puneet

You can capitalize the first letter of a text value in Google Sheets. A function can help you with this, or you can write a custom formula for it.

write-capitalize-the-first-letter-in-google-sheets

It is useful for correcting data entered inconsistently, where only the first character needs to be capitalized. So, let’s get started…

Use PROPER Function to Capitalize the First Letter

The PROPER function in Google Sheets converts a text to proper case, capitalizing the first letter of each word in the text string and converting all other letters to lowercase. This function is handy for formatting names, addresses, or other text values requiring standard capitalization.

Let’s say you have value in the cell A1, and you want to convert that value to

enter-the-closing-parentheses-in-google-sheets
  1. First, in a cell, enter the (=) to start the function and enter “PROPER” to enter the function.
  2. After that, enter the starting parentheses “(“ to know the arguments to define.
  3. Next, refer to the cell where you have the value for which you want to capitalize the first letter.
  4. In the end, enter the closing parentheses and then hit enter to get the result in the cell.

When you hit enter, it converts the first letter of each word into a capital letter.

=PROPER(A1)
converts-the-first-letter-of-each-word-into-a-capita-letter-in-google-sheets

Note – PROPER treats any characters separated by a space as separate words and will capitalize the first character of each + It does not provide exceptions for words that should not be capitalized in titles (like “and,” “the,” “of”).

Capitalize the First Letter of the First Word Only

There might be a situation where you only need to capitalize the first letter of the first word. You must write a formula using multiple functions (UPPER, LEFT, LOWER, RIGHT, and LEN). Below is the formula that you need to write:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
formula-using-multiple-functions-in-google-sheets

To understand this formula, you need to split it into parts:

understand-this-capitalize-the-first-letter-in-google-sheets

The first part of the formula is UPPER(LEFT(A1,1)). This changes the first letter to uppercase. Here’s how it works:

  • LEFT(A1,1): This function gets the string’s first character in cell A1.
  • UPPER(): This function converts the first character, obtained from the LEFT function, to uppercase.

The second part of the formula is LOWER(RIGHT(A1,LEN(A1)-1)). This changes the rest of the string to lowercase. Here’s how it works:

  1. RIGHT(A1,LEN(A1)-1): The RIGHT function gets all the characters in the string except for the first one. The LEN(A1)-1 calculates the length of the string and subtracts 1 to exclude the first character.
  2. LOWER(): This function is then used to convert all these characters obtained from the RIGHT function to lowercase.

In the end, you have an ampersand to combine both parts (the first letter of the first word and the rest of the part of the values) into a single value where you only have the first letter of the first word capitalized.

Note: If the first character in the text value is a number or a symbol, it will be returned as is (since UPPER only works with letters), and the subsequent letters will be converted to lowercase.

Last Updated: May 09, 2024