How to Capitalize the First Letter of a Sentence in Google Sheets

GiGiKing·March 12, 2024·0 comments

Want to learn how to capitalize the first letter of a sentence in Google Sheets? Look no further! While there is no built-in function for this in Google Sheets, I have a magical formula that can get the job done. It may seem a bit complex, but once you understand the text functions involved, it’s as easy as pie!

The Text Functions That Make it Possible

Before we dive into the formula, let’s take a quick look at the text functions that are involved in capitalizing the first letter of a sentence in Google Sheets. These functions are:

Join
Replace
Trim
Split
Upper
Left

Additionally, we’ll be using two more functions called Array_Formula and Transpose. These functions play a crucial role in making our formula work like magic.

The Magical Formula

Now, let’s get to the good stuff! Here is the single piece of formula that will capitalize the first letter of each sentence in Google Sheets:

=join(". ",ArrayFormula(replace(transpose(ARRAYFORMULA(TRIM(SPLIT( A1 , "." )))),1,1,upper(left(transpose(ARRAYFORMULA(TRIM(SPLIT( A1 , "." )))),1)))))&"."

To make things even clearer, imagine a sentence: “this is a sentence. it needs to be capitalized.” When you apply the formula to this text, the result will be: “This is a sentence. It needs to be capitalized.”

Capitalize the first letter in Google Sheets

Important Notes

There are a couple of things you need to keep in mind when using this formula:

In the formula, the cell A1 represents the cell containing the text you want to capitalize. Feel free to change the cell reference as needed.
The formula will capitalize the first letter after each full stop, except for the very first character of the text.

Putting it into Action

If you’d like to see the formula in action, I’ve created a Google Sheets document where you can test it out. Just click here to access the document.

The Core Part of the Formula

Every text function used in this formula is essential, but there is one function that is absolutely crucial and cannot be replaced. That function is Array_Formula. Without it, we wouldn’t be able to achieve the desired result.

I hope you find this Google Sheets tutorial useful! If you have any questions or thoughts, please feel free to share them in the comments below. Happy capitalizing!

Similar Reading: