How to change the case of text in Excel - Image by Mikes-Photography from PixabayIt is a frequent problem that I see. People do not realise that the Caps Lock Key is set on their keyboard. When in Excel they get frustrated that they have to retype because there is the wrong capitalization of a word or several cells containing words.

Well, yes retyping might be ok if you only have a few words to retype. But if you have not noticed for a long while, you may have a lot of cells to overtype. It would therefore make better sense to use a function in Excel that allows you to change the case of text in a cell.

Here is an example of a few rows in Excel example 1

What I want is just the first letter of the words in the cells A2:A5 to be capitalised.

Using a function

We are going to use the function [PROPER] to change the letters to the desired state.

  • Insert a new column at A by right mouse clicking on the name of the column A, and selecting [Insert] from the list.

Insert Column

  • In the new cell A2 type the following: [=PROPER(B2)] then press Enter.

The result looks like this.

Result 1

A different function

If you require that all the letters are in lower case then you need the function [Lower]

  • In the cell A3 type the formula [=lower(b3)]

The result is this.

Result 2

If you typed the text incorrectly in lower case and wish it to be upper case then use the function [Upper].

Continue the theme

You can autofill the formula down the column so that the text is all in the same case either all upper, all lower or all proper. The choice is yours.

Select the new format in cells A2:A5, and copy and paste, using paste special and select values, over the original text in B2:B5. Or move the cells A2:A5 over the cells in B2:B5. Save your file. You cannot simply copy and paste this formula. If you do you will be referencing the wrong cell to change the case and will not get what you desire as a result.

Be radical?

Of course, you could be radical and select the text in Excel, copy and paste it into Word and use the Word Change case function that has 5 different styles, then copy and paste back into Excel.

Here is the text copied from Excel into Word. from Excel to Word

  • Select the table.

From the [Home Tab] in the [Font group] you will find the Change case Tool. Change Case Tool

  • Select the dropdown arrow to the right and a list drops down. list of cases
  • From this you can select whichever effect you require.
  • Select the resulting table.
  • Copy it.
  • Go back to Excel
  • Right click on the starting cell, in this case B2 and select paste so that your new text overtypes the old text.Result 3

At the end of the day it is whatever way you feel most comfortable with and know that you get the results you want.

How to remove unwanted formatting in Word.

Formatting across sheets in Excel

Find only what you need in your document. Replace with different formatting.


Please enter your comment!
Please enter your name here