Capitalizing the first letters can make your spreadsheet look neat and organized. And it helps improve readability and professionalism.
Manually capitalizing the first letters in every cell can be a total drag. Fortunately, Google Sheets has an inbuilt function that takes care of this quickly.
To capitalize the first letters in Google Sheets, just type in =PROPER(cell), where the cell is the cell number with the text you want to be capitalized.
In this post, I’ll show you how to capitalize each word in a cell, capitalize only the first letter, and how to auto-capitalize the first letters in a sentence in Google Sheets.
How To Capitalize Each Word In A Cell In Google Sheets?
You can use the PROPER function to capitalize each word in a cell. The simple formula contains the syntax PROPER and the reference cell address.
The formula should look like =PROPER(A2)
Replace “A2” with the cell you want to capitalize, and voila! Your text will be adequately capitalized.
For instance, if you want to capitalize each word in cell B5, the formula is going to be:
=PROPER(B5)
You can apply the formula to other cells in the column by clicking and dragging the “+” icon (Fill handle) at the bottom-right of the cell.
The only drawback of using a formula is that you should have a separate cell for the calculated result. Unless you are hard coding as =PROPER(text), you cannot get the result into the same cell.
PRO-TIP: You can also create a placeholder sheet with raw data and hide it from view. Learn here how you can hide sheets that are placeholders.
Here’s the whole process (as a gif, as it’s much easier to explain that way):
How To Capitalize Only the First Letter Of a Sentence In A Cell?
There are instances where you’d want to capitalize only the first letter of a sentence instead of every word.
This can be done in Google Sheets through functions, but there isn’t a dedicated function, so we have to improvise and combine a few formulas.
We can combine the following:
Functions together to get it done. The formula is going to look like this:
=REPLACE(LOWER(A2),1,1,UPPER(LEFT(A2,1)))
In this formula, the REPLACE function takes the first letter in the specified cell and replaces it with its capitalized form. Meanwhile, the LOWER function converts all remaining letters in the cell to lowercase.
Just copy and paste this formula and replace A2 with the cell you want to be capitalized.
Again, you will need a cell as a source and another cell for the result. You cannot get the formatted text to the same cell.
Here’s what the process looks like:
You can also use UPPER, RIGHT, LEN, and LEFT to do this as well. The formula looks like this:
=UPPER(LEFT(A5,1))&RIGHT(A5,LEN(A5)-1)
How To Auto Capitalize First Letter Of a Sentence? (Capitalize Every Word In A Column)
If you want to capitalize the first letters of every word in a column, there’s a much easier way to do it than using a regular PROPER formula and replicating it.
To do this, we can combine the functions:
- ARRAYFORMULA,
- PROPER,
- LEN,
- And IF.
The formula will look like this:
=ARRAYFORMULA(IF(LEN(A2:A), PROPER(A2:A),))
The “ARRAYFORMULA” function applies to the entire range of cells rather than just one cell. And the “PROPER” function capitalizes the first letter of each word in the cell.
The “IF” function checks if there are any zeros or empty cells. If the cell is not empty, the formula continues to the next step.
The formula repeats this process for every cell in the range we specify, giving you a whole column with the first letter of each word automatically capitalized.
