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

Published on

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 that can capitalize the first letter of a sentence.

It’s a single formula that will do the trick. I’ve nested a few functions to capitalize the first letter of each sentence in a paragraph.

You can use my formula out of the box. However, if you wish to learn how I’ve done it, including the functions I’ve used, please follow the step-by-step instructions below.

Formula to Capitalize the First Letter of Each Sentence in Google Sheets

Since there is no SENTENCE() function in Google Sheets, you can use the following formula as an alternative:

=ARRAYFORMULA(LET(
   val, A1,
   parse_1, SPLIT(val, ". ", FALSE),
   val_new, TEXTJOIN(". ", TRUE, REPLACE(parse_1, 1, 1, UPPER(LEFT(parse_1, 1)))),
   parse_2, SPLIT(val_new, "? ", FALSE),
   IFERROR(TEXTJOIN("? ", TRUE,REPLACE(parse_2, 1, 1, UPPER(LEFT(parse_2, 1)))),
)))

Important Points:

  1. In this formula, the cell A1 contains the text to capitalize. You can change the cell reference very easily as it only appears once in the formula.
  2. The formula will capitalize the first letter of each sentence in the provided text.
  3. You can enter this formula in any blank cell.

Sample Text: the quick brown fox jumps over the lazy dog. is this a sentence or just a fragment? it seems like a complete thought.

The formula will return the text in sentence case as follows: The quick brown fox jumps over the lazy dog. Is this a sentence or just a fragment? It seems like a complete thought.

Example: Capitalize the first letter in a sentence

Formula Breakdown

The formula that capitalizes the first letter of each sentence uses the LET function to assign names to value expressions’ results and returns the result of the formula expression.

This avoids repetitive calculations and enhances the overall performance of the formula and its readability.

Syntax:

LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)

Step 1:

  • name1 is val and corresponding value_expression1 is A1.

Step 2:

  • name2 is parse_1 and corresponding value_expression2 is:
SPLIT(val, ". ", FALSE)

The SPLIT function splits the text in cell A1 (val) at the delimiter “. “. There are two characters in the delimiter: a period and a space. The FALSE specifies not to divide the text around each character.

Output: the quick brown fox jumps over the lazy dog in one cell and is this a sentence or just a fragment? it seems like a complete thought. in another cell.

Step 3:

  • name3 is val_new and value_expression3 is:
TEXTJOIN(". ", TRUE, REPLACE(parse_1, 1, 1, UPPER(LEFT(parse_1, 1))))

This is a combination of three formulas.

The LEFT function returns the first character of each part of the split text, and the UPPER function makes them uppercase.

The REPLACE function replaces the first character of each part with the uppercase characters returned by the LEFT and UPPER combo.

The TEXTJOIN joins these split texts, and the delimiter will be the one we used for splitting, which is “. “.

Output: The quick brown fox jumps over the lazy dog. Is this a sentence or just a fragment? it seems like a complete thought.

Step 4:

  • name 4 is parse_2 and formula_expression4 is:
SPLIT(val_new, "? ", FALSE)

This splits the output from step 3, and the delimiter is “? “.

Output: The quick brown fox jumps over the lazy dog. Is this a sentence or just a fragment in one cell and it seems like a complete thought. in another cell.

Step 5:

It’s the formula expression that returns the final output.

IFERROR(TEXTJOIN("? ", TRUE,REPLACE(parse_2, 1, 1, UPPER(LEFT(parse_2, 1))))

The role of this formula is the same as step 3, except the IFERROR wrapper ensures the final output doesn’t return #VALUE due to an empty value in the source cell.

Additional Tip

The above formula capitalizes the first letter of each sentence in a paragraph within a specified cell. In short, other than the very first character, it capitalizes the first character after a period followed by a space or a question mark followed by a space.

In addition, if you want to capitalize any character after a colon followed by a space, use my custom Named function TOSENTENCECASE.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

5 COMMENTS

  1. 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),"")

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.