How To Capitalize The First Letter In Google Sheets

Kieran Dixon Updated:  June 28, 2021

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:

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:

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

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:

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.

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:

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:

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:

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:

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:

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:

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

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:

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.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

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.

You might like:

MINA Google Sheets Function [With Quiz]

The MINA function in Google Sheets finds the minimum! Text or numbers... doesn't matter - MINA finds the smallest / most negative.

MIN Google Sheets Function [With Quiz]

The MIN function in Google Sheets finds the minimum! It doesn't matter how many numbers, MIN will quickly find the smallest / most negative.

How To Split Cells In Google Sheets

Need to split data? Click here to learn how to split text to columns horizontally, vertically, multiple delimiters, and fixed widths.