How To Capitalize The First Letter In Google Sheets
You want to capitalize the first letter in Google Sheets.
This can mean three things.
Click on the one you want:
- Capitalize the first letter of each word in a cell (or column)
- Capitalize only the first letter in a cell (or column)
- Capitalize the letters at the start each sentence in a cell (Sentence Case)
Capitalize The First Letter Of Each Word In A Cell In Google Sheets
Google Sheets has a built-in function for this:
To capitalize the first letter of each word while making the rest lowercase in a cell, it's as simple as referencing the text you need to change within the PROPER function:
A | B | C | |
1 | Text | Formula | Output |
2 | MICHAEL SCOTT | =PROPER(A2) | Michael Scott |
3 | jim halpert | =PROPER(A3) | Jim Halpert |
4 | DwIgHt ScHrUtE | =PROPER(A4) | Dwight Schrute |
5 | PAM beesly | =PROPER(A5) | Pam Beesly |
6 | Andy Bernard | =PROPER(A6) | Andy Bernard |
You can also reference text directly instead of using a cell reference:
Will output: Michael Scott
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
Capitalize The First Letter Of Each Word In A Column In Google Sheets
The PROPER function in combination with the ArrayFormula function can make this happen.
Ordinarily the PROPER function accepts single pieces of text as arguments. If you wrap PROPER with the ArrayFormula function you can force it to accept arrays of data (like a whole column).
To capitalize the first letter of each word while making the rest lowercase in a columns, reference the column of text you need to change within the PROPER and ArrayFormula functions.
Doing so allows you to use one formula for the whole column:
A | B | C | |
1 | Text | Formula | Output |
2 | MICHAEL SCOTT | =ArrayFormula(PROPER(A2:A)) | Michael Scott |
3 | jim halpert | Jim Halpert | |
4 | DwIgHt ScHrUtE | Dwight Schrute | |
5 | PAM beesly | Pam Beesly | |
6 | Andy Bernard | Andy Bernard |
You can quickly add the ArrayFormula function to any formula using the keyboard shortcut Ctrl+Shift+Enter for Windows and ⌘+Shift+Enter for Mac.
Capitalize The First Letter In A Cell In Google Sheets
Capitalizing only the first letter in a cell is a little more complex than capitalizing every word because there isn't a dedicated function.
Instead, you need to construct your own formula.
I'm going to show you how to construct this formula but if you just want one to copy and paste you can click here.
If you need to capitalize the first character regardless of whether it's a letter, click here.
Let's first identify issues we'll need to overcome:
1. What if the first letter isn't the first character?
If the text is something like "1. example text" you want the output to be "1. Example text". But if you only change the first character in a cell you'll just get "1. example text" again.
If you need to capitalize the first character instead of the first letter you can use either of these formulas (for a cell):
Or either of these formulas (for a column):
These formulas capitalize only the first character (not letter) and leave the rest untouched.
You need to find and change the first letter, not the first character.
2. What if there are proper nouns or additional sentences in the cell?
If the text is something like "go to New York" you want the output to be "Go to New York". But if you capitalized the first letter and change the rest to lowercase you get "Go to new york".
You need to leave the rest of the cell alone, changing only the first letter.
3. What if there are no letters in the text?
If the text is something like "123!" you want the output to be "123!".
You need your formula to not throw an error when there are no letters.
Now you know what you have to do, let's build that formula!
STEP 1: Extract the first letter (not character)
The REGEXEXTRACT function is perfect for this:
This function requires an understanding of regular expressions. I'm not going to cover that now, just know that they're a way to search text for specific patterns.
Here is a formula with and some example data:
A | B | C | |
1 | Text | Formula | Output |
2 | i am a little stitious | =REGEXEXTRACT(A2,"([A-Za-z]).*") | i |
3 | Prawn who yawns at dawn | =REGEXEXTRACT(A3,"([A-Za-z]).*") | P |
4 | 3. i am Beyoncé always | =REGEXEXTRACT(A4,"([A-Za-z]).*") | i |
5 | identity theft is not a joke | =REGEXEXTRACT(A5,"([A-Za-z]).*") | i |
6 | early worm gets the worm | =REGEXEXTRACT(A6,"([A-Za-z]).*") | e |
The regular_expression, ([A-Za-z]).*, finds the first time an uppercase or lowercase letter appears in the text. The whole formula finds and returns it.
There's a slight problem… If you try text that doesn't contain letters you get an error:
A | B | C | |
1 | Text | Formula | Output |
2 | 123! | =REGEXEXTRACT(A2,"([A-Za-z]).*") | #N/A |
You can fix this by wrapping the formula with the IFERROR function. That way if the REGEXEXTRACT function outputs an error the formula will instead output all of the original text:
A | B | C | |
1 | Text | Formula | Output |
2 | 123! | =IFERROR(REGEXEXTRACT(A2,"([A-Za-z]).*"),A2) | 123! |
STEP 2: Change the first letter (not character)
Now that you've got the first letter you can change it to an uppercase letter using either the UPPER function of the PROPER function.
Because it's only one character they both do exactly the same thing.
For clarity within the formula, I'd choose UPPER:
This function converts every letter in the text to uppercase.
With the first letter changed, it's time to replace it in the original text.
Here, the SUBSTITUTE function is the best option:
Because you can tell the function to only change the first occurrence, you can do the exact find and replace you need very easily.
STEP 3: Put it all together
You've got all of the pieces of this puzzle (REGEXEXTRACT, IFERROR, UPPER, and SUBSTITUTE) and now you can put them together.
The main function is SUBSTITUTE so start there:
- Text_to_search = cell reference (e.g. A2)
- search_for = the first letter (not character): REGEXEXTRACT(A2,"([A-Za-z]).*")
- replace_with = the capitalized first letter: UPPER(REGEXEXTRACT(A2,"([A-Za-z]).*"))
- [occurrence_number] = the first one: 1
The IFERROR function mentioned above sits on the outside of the entire formula as otherwise we'd need to include it twice (once in search_for and once in replace_with):
Here's the formula at work:
A | B | C | |
1 | Text | Formula | Output |
2 | i am a little stitious | =IFERROR(SUBSTITUTE(A2,REGEXEXTRACT(A2,"([A-Za-z]).*"),UPPER(REGEXEXTRACT(A2,"([A-Za-z]).*")),1),A2) | I am a little stitious |
3 | Prawn who yawns at dawn | =IFERROR(SUBSTITUTE(A3,REGEXEXTRACT(A3,"([A-Za-z]).*"),UPPER(REGEXEXTRACT(A3,"([A-Za-z]).*")),1),A3) | Prawn who yawns at dawn |
4 | 3. i am Beyoncé always | =IFERROR(SUBSTITUTE(A4,REGEXEXTRACT(A4,"([A-Za-z]).*"),UPPER(REGEXEXTRACT(A4,"([A-Za-z]).*")),1),A4) | 3. I am Beyoncé always |
5 | Identity theft is not a joke | =IFERROR(SUBSTITUTE(A5,REGEXEXTRACT(A5,"([A-Za-z]).*"),UPPER(REGEXEXTRACT(A5,"([A-Za-z]).*")),1),A5) | Identity theft is not a joke |
6 | early worm gets the worm | =IFERROR(SUBSTITUTE(A6,REGEXEXTRACT(A6,"([A-Za-z]).*"),UPPER(REGEXEXTRACT(A6,"([A-Za-z]).*")),1),A6) | Early worm gets the worm |
7 | 123! | =IFERROR(SUBSTITUTE(A7,REGEXEXTRACT(A7,"([A-Za-z]).*"),UPPER(REGEXEXTRACT(A7,"([A-Za-z]).*")),1),A7) | 123! |
Capitalize The First Letter In A Column In Google Sheets
To capitalize the first letter in an entire column you'll need to amend the formula above to accept arrays of data using the ArrayFormula function:
Now you can use one formula for the whole column:
A | B | C | |
1 | Text | Formula | Output |
2 | i am a little stitious | =ArrayFormula(IFERROR(SUBSTITUTE(A2:A,REGEXEXTRACT(A2:A,"([A-Za-z]).*"),UPPER(REGEXEXTRACT(A2:A,"([A-Za-z]).*")),1),A2:A)) | I am a little stitious |
3 | Prawn who yawns at dawn | Prawn who yawns at dawn | |
4 | 3. i am Beyoncé always | 3. I am Beyoncé always | |
5 | Identity theft is not a joke | Identity theft is not a joke | |
6 | early worm gets the worm | Early worm gets the worm | |
7 | 123! | 123! |
You can quickly add the ArrayFormula function to any formula using the keyboard shortcut Ctrl+Shift+Enter for Windows and ⌘+Shift+Enter for Mac.
Sentence Case In Google Sheets
Sentence case is the most complicated of the three options because you need:
- Separate the text into sentences
- Capitalize the first letter or character of each
- Join them back together so it matches the original text
There's a lot of room for error in there:
- What if the text is blank?
- What if it doesn't contain letters?
You can change a couple of the formulas already constructed above to get the desired result.
The only additional elements are the SPLIT and TRIM functions and the adding back of the period at the end of the text output (because the SPLIT function would remove it).
Here's a formula to capitalize the first letter (not character) of each sentence in a cell:
A | B | |
1 | Text | sentence one. 2 sentence two. and three. |
2 | Formula | =ArrayFormula(IFERROR(JOIN(".",IFERROR(SUBSTITUTE(SPLIT(B1,"."),REGEXEXTRACT(SPLIT(B1,"."),"([A-Za-z]).*"),UPPER(REGEXEXTRACT(SPLIT(B1,"."),"([A-Za-z]).*")),1),SPLIT(B1,".")))))&IF(RIGHT(B1,1)=".",".","") |
3 | Output | Sentence one. 2 Sentence two. And three. |
In the second sentence the first character is a number which gets ignored and instead the "s" is capitalized.
If instead you need to capitalize the first character (be it a letter, number or punctuation) of each sentence in a cell, you can use:
A | B | |
1 | Text | sentence one. 2 sentence two. and three. |
2 | Formula | =ArrayFormula(IFERROR(JOIN(". ",REPLACE(TRIM(SPLIT(B1,".")),1,1,UPPER(LEFT(TRIM(SPLIT(B1,".")),1))))))&IF(RIGHT(B1,1)=".",".","") |
3 | Output | Sentence one. 2 sentence two. And three. |
This time the second sentence remains unchanged as the first character "2" is capitalized.
Because these formulas rely on ArrayFormula to handle multiple sentences and JOIN to put them back together again they cannot be amended to work on entire columns.
You'll have to use one formula per row instead.
These formulas do not handle ellipses (...) or decimal numbers (1.1).
They could, but would become quite complex for the purposes of this article.
Keep this in mind when using them.
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.