Google Sheets Cheat Sheet

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:

  1. Capitalize the first letter of each word in a cell (or column)
  2. Capitalize only the first letter in a cell (or column)
  3. 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:

=PROPER(text_to_be_capitalised)

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:

ABC
1TextFormulaOutput
2MICHAEL SCOTT=PROPER(A2)Michael Scott
3jim halpert=PROPER(A3)Jim Halpert
4DwIgHt ScHrUtE=PROPER(A4)Dwight Schrute
5PAM beesly=PROPER(A5)Pam Beesly
6Andy Bernard=PROPER(A6)Andy Bernard

You can also reference text directly instead of using a cell reference:

=PROPER("MICHAEL SCOTT")

Will output: Michael Scott

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

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:

ABC
1TextFormulaOutput
2MICHAEL SCOTT=ArrayFormula(PROPER(A2:A))Michael Scott
3jim halpertJim Halpert
4DwIgHt ScHrUtEDwight Schrute
5PAM beeslyPam Beesly
6Andy BernardAndy 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):

=REPLACE(A2,1,1,UPPER(LEFT(A2,1)))
=UPPER(LEFT(A2,1))&RIGHT(A2,LEN(A2)-1)

Or either of these formulas (for a column):

=ArrayFormula(REPLACE(A2:A,1,1,UPPER(LEFT(A2:A,1))))
=ArrayFormula(IFERROR(UPPER(LEFT(A2:A,1))&RIGHT(A2:A,LEN(A2:A)-1)))

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:

=REGEXEXTRACT(text, regular_expression)

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:

ABC
1TextFormulaOutput
2i am a little stitious=REGEXEXTRACT(A2,"([A-Za-z]).*")i
3Prawn who yawns at dawn=REGEXEXTRACT(A3,"([A-Za-z]).*")P
43. i am Beyoncé always=REGEXEXTRACT(A4,"([A-Za-z]).*")i
5identity theft is not a joke=REGEXEXTRACT(A5,"([A-Za-z]).*")i
6early 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:

ABC
1TextFormulaOutput
2123!=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:

ABC
1TextFormulaOutput
2123!=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:

=UPPER(text)

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:

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

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:

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

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):

=IFERROR(SUBSTITUTE(A2,REGEXEXTRACT(A2,"([A-Za-z]).*"),UPPER(REGEXEXTRACT(A2,"([A-Za-z]).*")),1),A2)

Here's the formula at work:

ABC
1TextFormulaOutput
2i 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
3Prawn 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
43. 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
5Identity 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
6early 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
7123!=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:

=ArrayFormula(IFERROR(SUBSTITUTE(A2:A,REGEXEXTRACT(A2:A,"([A-Za-z]).*"),UPPER(REGEXEXTRACT(A2:A,"([A-Za-z]).*")),1),A2:A))

Now you can use one formula for the whole column:

ABC
1TextFormulaOutput
2i 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
3Prawn who yawns at dawnPrawn who yawns at dawn
43. i am Beyoncé always3. I am Beyoncé always
5Identity theft is not a jokeIdentity theft is not a joke
6early worm gets the wormEarly worm gets the worm
7123!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:

  1. Separate the text into sentences
  2. Capitalize the first letter or character of each
  3. Join them back together so it matches the original text

There's a lot of room for error in there:

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:

=ArrayFormula(IFERROR(JOIN(".",IFERROR(SUBSTITUTE(SPLIT(A1,"."),REGEXEXTRACT(SPLIT(A1,"."),"([A-Za-z]).*"),UPPER(REGEXEXTRACT(SPLIT(A1,"."),"([A-Za-z]).*")),1),SPLIT(A1,".")))))&IF(RIGHT(A1,1)=".",".","")
AB
1Textsentence one. 2 sentence two. and three.
2Formula=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)=".",".","")
3OutputSentence 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:

=ArrayFormula(IFERROR(JOIN(". ",REPLACE(TRIM(SPLIT(A1,".")),1,1,UPPER(LEFT(TRIM(SPLIT(A1,".")),1))))))&IF(RIGHT(A1,1)=".",".","")
AB
1Textsentence one. 2 sentence two. and three.
2Formula=ArrayFormula(IFERROR(JOIN(". ",REPLACE(TRIM(SPLIT(B1,".")),1,1,UPPER(LEFT(TRIM(SPLIT(B1,".")),1))))))&IF(RIGHT(B1,1)=".",".","")
3OutputSentence 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

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.