Want to capitalize the first letter of a sentence in Google Sheets? There is no built-in function in Google Sheets to do this. But I have a formula which is a little bit complex but can capitalize the first letter of a sentence.
It’s a single piece of formula that will do the trick. Actually, I’ve nested few text functions to capitalize the first letter of each sentence.
You can use my formula out of the box. Still, if you wish to learn how I’ve done it, I mean the text functions that I’ve used, find the links below. After that, you can find my magical combo formula.
The Text Functions That Involved in Capitalizing the First Letter of a 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 the First Letter of a Sentence
Since there is no SENTENCE function in Google Sheets, you can use my below formula as an alternative to the 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. In this formula, the cell A1 contains the text to capitalize. You can change the cell reference in the formula.
2. Other than the first character, this formula would capitalize the very first letter after each full stop in a sentence.
Do you want the original sheet where I’ve applied this formula? No issue. Here is the link to the Google Sheets that containing the above formula to capitalize the first letter of a Sentence in Google Sheets.
https://docs.google.com/spreadsheets/d/1XExI1YSG2y0A2FqQ3Pk5uoTscEDc12cc3d2Tqfgihcc/edit?usp=sharing
What is the Core Part of this Formula:
All the text functions in this formula are important. You can replace that with Regex Functions also. But there is one formula, that is a must and no replacement available. It’s the Array_Formula. Without that, I couldn’t have achieved this.
Find this Google Sheets Tutorial useful? Please drop your views in the comments below. Enjoy.
Similar Reading:
1. How to Apply Bulk Change Case in Google Sheets Using Query Function
2. Google Doc Spreadsheet – Change Text to Upper, Lower and Sentence Case
Assuming the first character of the source text is a letter, here’s one I came up with on the fly:
=Upper(Left(A1,1)) & Right(A1, Len(A1)-1)This will fail if it’s fed an empty cell but you could capture that with a conditional error check, as per:
=IFERROR(Upper(Left(A1,1)) & Right(A1, Len(A1)-1),"")It works perfectly for me. Thank you.
=proper(left("desired_cell"))&RIGHT("desired_cell",LEN("desired_cell")-1)This formula achieve the same thing.
If I change some of the letter to caps in the sample cell. The formula does not correct those to lower case.
Hi, Andy,
This formula won’t do that. The purpose of this formula is to only capitalize the first letter in a sentence. You can try the built-in PROPER function.
https://infoinspired.com/google-docs/spreadsheet/google-sheets-function-guide/