# How to Change to Sentence Case in Excel [Formula]

- Written by Puneet

In Excel, we have three specific functions to convert text from one case to another. Whereas there is another case for which we don’t have any function or any other option.

That’s ⇢ Sentence Case

## What Sentence Case Actually Is?

In sentence case, for an entire sentence, the first letter of the first word should be a capital letter and the rest of the letters of the first word and all the other words should be in lowercase.

Now, the thing is. How can we convert a normal text to a sentence case in Excel if we don’t have any particular function or option?

The answer is simple. We can create a formula by combining other text functions. So today, in this post, I’d like to share with you a simple formula that you can use to convert a normal case to a sentence case in Excel.

## Convert to Sentence Case in Excel

To create a formula for sentence cases we need to use five different text functions. Let your text is in cell A1, then the formula will be:

``=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))``

### How it Works

To understand this formula, we need to split it into two parts.

In the first part, the left function returns the first character and then the upper function converts it into the upper case.

In the second part, we will convert the rest of the text to lowercase as we need the rest of the letters in lowercase for sentence case.

And finally, this combination returns the text in sentence case.

Yes, that’s it.

## Convert to Sentence Case for More than One Sentence

The formula we have learned above is simple and easy to apply. But, do you have the same thought in your mind that I have right now?

What will we do if there is more than one sentence in a cell? This is crazy: And you know, in this situation above formula is for nothing, we can’t use it.

We need a different formula that can convert a text into a sentence case when we have more than one sentence.

``=SUBSTITUTE(SUBSTITUTE(PROPER(SUBSTITUTE(SUBSTITUTE(LOWER(C12),". ","9z9")," ","zxz")),"9Z9",". "),"zxz"," ")``

### How it Works

So, you want to learn how this formula works? Right? Well, you need to hold your nerves and stay with me for a minute. We need to split this formula into three different parts as I have done below.

In the first part, with the lower function and two substitute functions, we have converted the text into lower case and replaced all the spaces within words with “zxz” and space and dot between two sentences with “9z9”.

At this point, we don’t have any space in our text. So far so good.

In the second part, we have converted the entire text into the proper case using the proper function.

A secret you need to learn: Whenever you convert a text into a proper case and you have a number in that text, the function will treat that text as separate words. Look at the below example.

In the third part, we have again used two substitute functions to replace “zxz” with space and “9z9” with a dot and space.

In this way, we have converted our multiple-sentence text into an actual sentence case text.

## Conclusion

Sometimes we use Excel to type some sort of text and there is the probability to type it in the wrong case. And, if you know to convert a text into a sentence case, you can correct your text easily.

If you have a sentence where you have a question mark instead of a dot then you can use the question mark in the formula and the rest of the formula will be the same. I hope this formula tip will help you in your work. Now, tell me one thing.

Do you have any other method to do this text conversion?

## 8 thoughts on “How to Change to Sentence Case in Excel [Formula]”

1. Is there a way that this can be done using Power Query?

2. This is where your blog is unique. you give practical stuff never heard of elsewhere and provide a lucid explanation with example files.

Though nothing too advanced which is excel guru level but useful stuff that others wouldnt have thought of

3. Puneet, nice formula.

Problem is that it does not play well with punctuation and other non-alphabet characters (eg , . ] 🙂

It can be corrected with a further SUBSTITUTE(“Zxz”,” “) in the formula

Paul :Zxz)

• Paul, can you restate the entire formula with the added substitute to fix the comma problem? I really need the formula to work with commas in sentences, and I can’t figure out how/where to add the extra substitute you suggested. Additionally, can we add another substitute to get the formula to handle apostrophes? If yes, what would that formula look like?