As Puneet Gogia mentions in his post, we can change text using UPPER, LOWER and PROPER functions but we don’t have a SENTENCE function to capitalize the first letter of each sentence. Puneet solves it with a very interesting formula. How exactly does it work? Let’s audit it!

Text

I changed the sample text so that we have three sentences and “2nd” in the middle.

this is a test. second 2nd test. third test.

The result should look like this (first letter of each sentence is capitalized)

This is a test. Second 2nd test. Third test.

Puneet’s Formula

I added color to the different parts to make it more readable.

Start From the Inside Out

Let’s start with the purple part that is in the middle. Everything else is wrapped around it.

SUBSTITUTE(LOWER(A1),”. “,”9z9”)

Change everything to lower case. Substitute “. “ (end of sentences) with “9z9”. We get this:

this is a test9z9second 2nd test9z9third test.

SUBSTITUTE(this is a test9z9second 2nd test9z9third test.,” “,”zxz”)

Next, substitute all remaining spaces with “zxz”. We get this:

thiszxziszxzazxztest9z9secondzxz2ndzxztest9z9thirdzxztest.

PROPER(“thiszxziszxzazxztest9z9secondzxz2ndzxztest9z9thirdzxztest.“)

Here is the key part. After each number the next letter gets capitalized. We end up with:

Thiszxziszxzazxztest9Z9Secondzxz2Ndzxztest9Z9Thirdzxztest.

Notice in the text above that the first letter in each sentence (‘This’ ‘Second’ ‘Third’) are capitalized. HOWEVER, the “N” in “2nd” is also capitalized. Maybe it’s not important….but what if it is?

=SUBSTITUTE(SUBSTITUTE(

Putting it back together: the two substitutes to the left of the PROPER function reverse the original substitutions. We get:

This is a test. Second 2Nd test. Third test.

Review

Puneet’s formula is amazing! ….Except numbers with a letter attached to the end are capitalized.

In his original text “with 1000s” becomes “with 1000S“. In my text example the “2nd” becomes “2Nd“. 

It’s a minor detail and maybe it doesn’t matter.  But if it does matter what can we do?

My Solution

I spent some time thinking of how to modify Puneet’s formula. Then I thought about an array formula…YIKES! It started to get complicated. VBA? Possible, but it would take me hours.

What about helper formulas? Using various formulas in a step by step approach is sometimes frowned upon. Over the years I have embraced it. Why? Instead of one monster formula or vba code that few understand helper formulas allow us to break the solution into understandable pieces. It’s much easier to teach the solution to someone else and also easier to audit later on.

Download my Excel file–>  sentence-case (Puneet & Kevin).xlsx.

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’m a Data Analyst.

There are many amazing data tools out there. Microsoft Excel is one of them and it’s the universal tool that everyone has!

Check out my YouTube videos and blog.