How to Change All Caps to Lowercase Except the First Letter in Excel?

by

Data that has been imported to Excel from external sources often need to be formatted properly, both for aesthetic as well as functional purposes.

If you’ve imported data that contains text in all caps, you might need to change the letters to lowercase to improve its readability.

Sometimes you might need to ensure that only the first letter of each word in the sentence is capitalized, while the rest of the alphabets are converted to lowercase.

Unlike MS Word, Excel does not have a button or menu item to change the capitalization of your letters.

So if you need to change lowercase letters in your worksheet to uppercase or vice-versa, you will need to use alternative methods.

Simply changing all caps to lowercase is quite simple. All you need to do is pass the text as a parameter to the LOWER function.

But what if you want to keep only the first letter of the text capitalized while converting all the other letters to lowercase?

In this tutorial, we will look at two cases for changing capitalization in Excel:

  • When you want to convert all caps to lowercase, but keep the first letter of every word in uppercase (as in titles and names)
  • When you want to convert all caps to lowercase but keep the first letter of the first word in upper case (as in a simple sentence)

Change All Caps to Lowercase (except the First Letter of Every Word)

Let us consider the following list of strings:

Names dataset

Notice that all the strings are in all caps.

If you want to convert these strings to lowercase, while keeping the first letter of each word in caps, then the PROPER function would be a good option.

The PROPER function is used to convert a given text to a proper case.

This means that the function changes the first character of each word to uppercase, and the rest of the alphabets of each word are converted to lower case.

This is irrespective of whether your original text is in all caps, all lowercase letters, or a mix of upper and lowercase letters.

The syntax for the PROPER Function

The syntax for the PROPER function is very simple:

PROPER(text)

Here, the text is the input text that you want to convert to the proper case.

This could be either a string value or a reference to a cell containing the string that you want to convert.

To convert the strings in our sample data to proper case, simply type the following formula in the first cell where you want the result displayed (cell B2 in our example):

=PROPER(A2)

Then use the fill handle of the cell to copy the formula down to the rest of the column.

Here’s the result you should get:

PROPER function to change all caps to lower case

Notice that the first letter of each word is capitalized while the rest of the letters are in lowercase.

Also read: How to Convert to Sentence Case in Excel?

Change All Caps to Lowercase (except First Letter of the First Word)

Now let us see a case where you want to keep only the first letter of the first word capitalized while converting all the other letters to lowercase.

This can be achieved by using a combination of LOWER, RIGHT, and LEFT functions.

Before we see how the three functions can be combined to serve our purpose, let us quickly look at how each of these three functions works individually.

The LOWER Function

This function takes a string or reference to a cell containing a string and converts it to lowercase.

For example, the formula: =LOWER(A2) in the screenshot below converts the string in A2 to lowercase:

LOWER formula result

The LEFT Function

This function extracts a given number of characters from the left side of a string. It takes two parameters:

  • A string or reference to a cell containing a string
  • An integer that specifies how many characters we want to extract from the left side of the string.

For example, the formula: =LEFT(A2,2) extracts only the first 2 characters from the string in cell A2:

LEFT formula result

The RIGHT Function

This function extracts a given number of characters from the right side of a string. It takes two parameters:

  • A string or reference to a cell containing a string
  • An integer that specifies how many characters we want to extract from the right side of the string.

For example, the formula: =RIGHT(A2,2) extracts only the last 2 characters from the string in cell A2:

RIGHT formula result

Let’s now combine these three functions to change all caps of the following list of sentences to lowercase, except the first letter:

Sentence in All Caps dataset

To accomplish this, we can use the following formula (to convert the string in cell A2):

=LEFT(A2,1)&LOWER(RIGHT(A2,LEN(A2)-1))

To copy this formula to the other cells in the column, simply drag down the fill handle of cell A2. 

Here’s the result we get:

Formula to change all caps to lower case except the first word

As can be seen from the above screenshot, the first letter for each sentence was retained in uppercase while the rest of the letters were converted to lowercase.

Explanation of the Formula

In the above formula, we are essentially performing the following actions:

  • Converting all the letters except the first one into lowercase.
  • Retaining the first letter as it is

Let us break down the formula to understand it better:

  • LEN(A2)-1: This finds the length of the string in cell A2 and subtracts 1 from it, because this will give us the number of cells from the right that we want to convert to lowercase. Since the string in cell A2 contains 40 characters, the above formula will result in the value 40-1=39.
  • RIGHT(A2, LEN(A2)-1): This extracts the rightmost 39 characters from the string in cell A2. In other words, this function will return the last 39 characters from the string in A2.
  • LOWER(RIGHT(A2, LEN(A2)-1)): This simply converts the last 39 letters extracted into lowercase.
  • LEFT(A2,1): This extracts the leftmost character from the string in cell A2. In other words, this function will return the first character from the string in A2.
  • LEFT(A2,1)& LOWER(RIGHT(A2, LEN(A2)-1)): This simply combines the results obtained from the formulas LEFT(A2,1) and LOWER(RIGHT(A2, LEN(A2)-1)). So it converts the last 39 letters to lowercase, appends it to the first letter (which is already in uppercase) and returns the string obtained.

In a nutshell, here’s what’s happening when you break down the formula:

=LEFT(A2,1)&LOWER(RIGHT(A2,LEN(A2)-1))

= LEFT(A2,1)&LOWER(RIGHT(A2,39))

= LEFT(A2,1)&LOWER(“OMETIMES IT IS BETTER TO JUST WALK AWAY”)

=LEFT(A2,1)&”ometimes it is better to just walk away”

=”S”&”ometimes it is better to just walk away”

=”Sometimes it is better to just walk away”

In this tutorial, we covered two cases for changing capitalization in Excel.

You can use the first method (using the PROPER function) when you have titles or names, which require the first letter of every word to be capitalized, keeping all other letters in lowercase. 

The second method (using LOWER, RIGHT, and LEFT functions) can be used when you have simple sentences, which require only the first letter of the first word to be capitalized, keeping all other letters in lowercase.

Both methods are simple and quick to apply. We hope you find this helpful.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment