How to Capitalise First Letter of a Sentence in Google Sheets [Formula]

0
135
Capitalise First Letter of a Sentence in Google Sheets

Want to capitalise first letter in Google Sheets? There in so built-in formula to do this. But I’ve a formula which is little bit complex to capitalise first letter of a sentence in Google Sheets. But you needn’t worry. It’s a single piece of formula that will do the trick. Actually I’ve nested few text functions to capitalise first letter of each sentence in Google Sheets.

You can directly use the formula. Still if you wish to learn how I did it, here is the functions I’ve used. After that we can go to the formula.

Functions Involved to Capitalise First Letter of Sentence in Google Sheets

1. Join | 2. Replace | 3. Trim | 4. Split | 5. Upper | 6. Left

The other two functions are Array_Formula and Transpose.

Single Piece of Formula to Capitalise First Letter of a Sentence in Google Sheets

Since there is no SENTENCE function in Google Sheets, you can use my below formula as an alternative to Sentence() function.

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

Things to take care of:

1. Master text is in Cell A1, so you can change the formula based on your text in Cell.

2. After key in this formula in your own Google Doc Spreadsheet, you should rewrite the double quotes. This’s because my site theme might interfere with quotes. So you must replace it again.

3. This formula is capitalising the very first letter after a full stop in the sentence.

Do you want the original sheet where I’ve applied this formula? No issue. Here is the link to the Google Sheet that containing the above formula to capitalise first letter of a Sentence in Google Sheets.

https://docs.google.com/spreadsheets/d/1XExI1YSG2y0A2FqQ3Pk5uoTscEDc12cc3d2Tqfgihcc/edit?usp=sharing

What is the Core of this Formula:

All the text functions in this formula are important, but you can replace that with Regex Functions. But one formula, that is a must and no replacement available here is the Array_Formula. Without that, I couldn’t achieved this.

Find this Google Sheets Tutorial useful? Please drop your views in the comments below. Enjoy.

LEAVE A REPLY

Please enter your comment!
Please enter your name here