You might be looking for ways to format data on your Google sheets, quickly and easily. You copied your data and you realize oops.. now I need to change the word/sentence/Letter to upper case or lower case. If it’s a change in just one or two cells, you might prefer doing that manually. But imagine you are working on huge data, Then automatic capitalization in Google sheets will be your preferred choice.
You can capitalize the first letter, word or sentence using a PROPER or UPPER function in Google Sheets. We will go over various functions, that can help you format your data the way you like. If you don’t want to use these functions, you can use the ChangeCASE add-on, which is also covered in this article.
Capitalize the first letter in Google sheets using PROPER function
If you want to capitalize the first letter of each word in a cell, you can use the PROPER function. It might be required in cases of Proper nouns, like names and locations.
If you want to apply the Proper function for cell A1 and get the output in cell B1.
Then click on cell B1 and type =PROPER(A1) and you are done.
If you want to apply the same function for the remaining rows, you can just drag the “+” sign on the bottom-right of the cell to the next rows.
For example – I have “jeff parkinson” in my cell A1, which is in a lower case currently and I need to capitalize the first letter of Cell A1.
Also, I want the new output in Cell B1. To achieve this, I will type =PROPER(A1) in my B1 cell and press Enter.
“Jeff Parkinson” will be the final output. You can see the first letter is capitalized now as shown in the image.
A1 - "jeff parkinson"
B1 function - =PROPER(A1)
B1 output - "Jeff Parkinson"
Captialize all words in Google sheet using UPPER function
If you want to convert all your letters in a cell to uppercase you can use the function – “UPPER“.
Let’s take the same example again, To capitalize all words of “jeff parkinson” in cell A2, I will type function =UPPER(A2) in Cell B2 as shown in the image.
A2 - jeff parkinson
B2 function - =UPPER(A2)
B2 output - JEFF PARKINSON
Captialize all words in Google sheet using Fonts
There are some fonts also which can capitalize all your text if you apply them. Below mentioned is the list of few fonts.
- Bebas Neue
- JULIUS SANS ONE
- NOTABLE
- AMATIC SC
To achieve this –
- Select the single or multiple cells in your Google sheet
- Select “Font” from the menu ribbon and click on Drop down arrow.
- Choose “More fonts“.
- Search for any of the listed font. For example Bebas Neue.
- As soon as you will choose this font, text in your Cell will convert to upper case and your default Arial font will also change to Bebas Neue or your prefered selected font.
If you will see the all the text in Column A has been capitalized after applying above listed fonts as shown in the image.
Make your text Lowercase using LOWER function in Google Sheets
If you want to make all your text lowercase, then you can use the “LOWER” function in Google sheets.
For example, I have the text “JEFF PARKINSON” in the A2 cell. As soon as I will type =Lower(A2) in Cell B2 and press Enter. All text will change to lowercase as “jeff parkinson” as shown in the image.
A2 - JEFF PARKINSON
B2 function - =LOWER(A2)
B2 output - jeff parkinson
Capitalize First letter of each Cell in Google Sheets
If you want to capitalize the first letter of each cell, there is no direct function in Google sheets for that, But few functions can be combined to achieve this output. Here we are combining functions UPPER, LOWER, LEFT, RIGHT and LEN.
A2 - hi. how are you
B2 function - =UPPER(LEFT(A2,1))&LOWER(RIGHT(A2,LEN(A2)-1))
B2 output - Hi. how are you
If you will see in the below-mentioned image, Only the first letter is capitalized (i.e. H) of the statement “Hi. how are you” in Cell A2. Because I gave 1 in (Number of character in argument).
UPPER and LOWER functions have been covered above. Let’s talk about the LEFT, RIGHT and LEN functions used in the example.
LEFT (Cell_number,n) – Will return the n number of characters from left from Cell Cell_number.
RIGHT (Cell_number,n) – Will return the n number of characters from right from Cell Cell_number.
LEN(Cell_number) – Will return number of characters in cell cell_number
Pro Tip* If you want to capitalize the initial two letters, then provide "2" as arguments in LEFT and LEN FUNCTION. Then the function will look like =UPPER(LEFT(A2,2))&LOWER(RIGHT(A2,LEN(A2)-2)). B2 Output will be - HI. how are you You can see initial two character are capitalized now i.e. HI. So just need to change {n=number of characters} in Upper and Lower function to capitalize any number of letters in Google Sheet
Capitalize all words using “ArrayFormula” function in Google Sheets
If you need to apply the functions upper, lower and proper in multiple cells and don’t wish to use the drag option. You can use the function ArrayFormula.
=ARRAYFORMULA(UPPER(A2:B3))
This will convert all data in cell range to uppercase as shown in the image. For example, e.g. Text in cell Range A2:B3 is capitalized.
Capitalize First letter of each sentence in Google Sheets
Again, no direct function to capitalize the first letter of each sentence in a cell, but various functions can be combined to do so. Here we are combining functions UPPER, LOWER, JOIN, REPLACE, TRIM, SPLIT.
Check out this example-
A2 - welcome to this page. hope you like it. B2 function - =JOIN(". ",ArrayFormula (REPLACE(TRIM(SPLIT(A2,".")),1,1,UPPER(LEFT(TRIM(SPLIT(A2,".")),1))))) B2 Output - "Welcome to this page. Hope you like it."
If you will see text in cell A2 “welcome to this page. hope you like it” is capitalized to “Welcome to this page. Hope you like it.”
Please note: “1″ is the number of characters (n) as explained earlier. Just change the number if you want to make more than one character capitalized in the JOIN function.
Deleting duplicate data after in Google sheets
You have applied these functions and got the data in the desired format in column B, but now you have two columns with the same set of data. If you delete your original data in column A, column B will give errors, and your desired data is lost.
To solve this problem, copy the data in column B.
Go to cell in column C, right-click and select Paste special-> paste values only.
Now you can delete, columns A and B and your duplicate data have been deleted, and only formatted data is left on the sheet.
Capitalize using Add-ons in Google Sheet – (Easiest Method)
If you are not comfortable with using functions, you can easily use these features by installing add-ons on your Google sheets.
- Go to add-ons in the Menu. Click Get add-ons > Search and install ChangeCASE add on.
The ChangeCASE add-on offers various options for capitalization.
To apply these functions, select all the cells on which you want to apply any specific option — > Go to Add-ons – > ChangeCase -> Select the format of your desire.
Options explained –
- All uppercase: All uppercase is similar to the “UPPER” function, and will convert data to uppercase.
- All lowercase: All lowercase is similar to the “LOWER” function, and will convert data to lowercase.
- First letter capitals: The first letter capitalise similar to the “PROPER” function, and will convert the first letter of each word uppercase.
- Invert Case: Invert case will convert uppercase letters to lowercase and lowercase letters to uppercase.
Invert case example as shown in the image –
Sentence Case: The sentencing case will change the first letter of each sentence to uppercase and the remaining letters to lowercase.
Title Case: Title case will convert the first letter of all words to uppercase, except supporting words used for prepositions, conjunctions, articles etc.
Example of Title case –
Conclusion
Further, you can check out the list of all functions available in Google Sheet. But if you are not very technical and don’t enjoy using functions. You can use Add-ons to achieve the same functionality. And that’s the good thing about Google sheets of Google docs suite.
Hope you enjoyed reading this article and found it helpful. I love reading your comments, so you may leave feedback or suggestions via the comment box.