4 ways for changing case in Excel

In this article I'd like to tell you about different ways to change Excel uppercase to lowercase or proper case. You'll learn how to perform these tasks with the help of Excel lower/upper functions, VBA macros, Microsoft Word, and an easy-to-use add-in by Ablebits.

The problem is that Excel doesn't have a special option for changing text case in worksheets. I don't know why Microsoft provided Word with such a powerful feature and didn't add it to Excel. It would really make spreadsheets tasks easier for many users. But you shouldn't rush into retyping all text data in your table. Fortunately, there are some good tricks to convert the text values in cells to uppercase, proper or lowercase. Let me share them with you.

Excel functions for changing text case

Microsoft Excel has three special functions that you can use to change the case of text. They are UPPER, LOWER and PROPER. The upper() function allows you to convert all lowercase letters in a text string to uppercase. The lower() function helps to exclude capital letters from text. The proper() function makes the first letter of each word capitalized and leaves the other letters lowercase (Proper Case).

All three of these options work on the same principle, so I'll show you how to use one of them. Let's take the Excel uppercase function as an example.

Enter an Excel formula

  1. Insert a new (helper) column next to the one that contains the text you want to convert.
    Insert a new column to enter formulas in the adjacent blank cells.

    Note: This step is optional. If your table is not large, you can just use any adjacent blank column.

  2. Enter the equal sign (=) and function name (UPPER) in the adjacent cell of the new column (B3).
  3. Type in the appropriate cell reference in the parentheses (C3) after the function name.

    Your formula should look like this =UPPER(C3), where C3 is the cell in the original column that has the text for conversion. Enter the equal sign, function name and cell reference to apply the formula to the adjacent cell.

  4. Click Enter. Click Enter to see the converted text value.

    As you can see in the screenshot above, cell B3 contains the uppercase version of the text from cell C3.

Copy a formula down a column

Now you need to copy the formula to other cells in the helper column.

  1. Select the cell that includes the formula.
  2. Move your mouse cursor to the small square (fill handle) in the lower-right corner of the selected cell until you see a small cross. Select the cell and drag the fill handle down to copy the formula.
  3. Hold the mouse button and drag the formula down over the cells where you want it to apply.
  4. Release the mouse button. Release the mouse button to fill the column with the text in uppercase.

    Note: If you need to fill the new column down to the end of the table, you can skip steps 5-7 and just double-click on the fill handle.

Remove a helper column

So you have two columns with the same text data, but in different case. I suppose you'd like to leave only the correct one. Let's copy the values from the helper column and then get rid of it.

  1. Highlight the cells that contain the formula and press Ctrl + C to copy them. Press Ctrl + C to copy the values from the selected cells.
  2. Right-click on the first cell in the original column.
  3. Click on the Values icon under Paste Options in the context menu. Choose Values from the Paste Options in the context menu to insert the text without the formula.

    Since you need only the text values, pick this option to avoid formula errors later.

  4. Right-click the selected helper column and choose the Delete option from the menu.
  5. Pick Entire column in the Delete dialog box and click OK. Select the helper column and choose the Delete option from the right-click menu to remove the column.

Here you are! Delete the helper column to get the table with the converted text case.

This theory might look very complicated to you. Take it easy and try to go through all these steps yourself. You'll see that changing case with the use of Excel functions is not difficult at all.

Use Microsoft Word to change case in Excel

If you don't want to mess with formulas in Excel, you can use a special command for changing text case in Word. Feel free to discover how this method works.

  1. Select the range where you want to change case in Excel.
  2. Press Ctrl + C or right-click on the selection and choose the Copy option from the context menu. Click Copy or press Ctrl + C to copy the table from Excel to Word.
  3. Open a new Word document.
  4. Press Ctrl + V or right-click on the blank page and select the Paste option from the context menu Click Paste or press Ctrl + V to paste the table in Word.

    Now you've got your Excel table in Word.

  5. Highlight the text in your table where you want to change the case.
  6. Move to the Font group on the HOME tab and click on the Change Case icon.
  7. Pick one of 5 case options from the drop-down list. Click on the Change Case icon to pick the case you want.

    Note: You can also select your text and press Shift + F3 until the style you want is applied. Using the keyboard shortcut you can choose only upper, lower or sentence case.

    Select the changed table or columns to copy back to Excel.

Now you have your table with the text case converted in Word. Just copy and paste it back to Excel. Click Paste or press Ctrl + V to paste the changed table back in Excel.

Converting text case with a VBA macro

You can also use a VBA macro for changing case in Excel. Don't worry if your knowledge of VBA leaves much to be desired. A while ago I didn't know much about it as well, but now I can share three simple macros that make Excel convert text to uppercase, proper or lowercase.

I won't labor the point and tell you how to insert and run VBA code in Excel because it was well described in one of our previous blog posts. I just want to show the macros that you can copy and paste into the code Module.

If you want to convert text to uppercase, you can use the following Excel VBA macro:

Sub Uppercase() For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = UCase(Cell.Value) End If Next Cell End Sub

To apply Excel lowercase to your data, insert the code shown below into the Module window.

Sub Lowercase() For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = LCase(Cell.Value) End If Next Cell End Sub

Pick the following macro if you want to convert your text values to proper / title case.

Sub Propercase() For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = _ Application _ .WorksheetFunction _ .Proper(Cell.Value) End If Next Cell End Sub

Quickly change case with the Cell Cleaner add-in

Looking at the three methods described above you might still think that there is no easy way to change case in Excel. Let's see what the Cell Cleaner add-in can do to solve the problem. Probably, you'll change your mind afterwards and this method will work best for you.

  1. Download the add-in and install it on your computer.

    After the installation the new Ablebits Data tab appears in Excel.

  2. Select the cells where you want to change the text case.
  3. Click on the Change Case icon in the Clean group on the Ablebits Data tab. Click on the Change Case icon in the Clean group on the Ablebits Data tab to display the Change case section.

    The Change case pane displays to the left of your worksheet.

  4. Select the case you need from the list.
  5. Press the Change case button to see the result. Select the necessary radio button to change the case of text in your Excel table.

    Note: If you want to keep the original version of your table, check the Back up worksheet box.

    Press the Change case button to convert the text to uppercase in Excel.

With Cell Cleaner for Excel the changing case routine seems to be much easier, doesn't it?

Besides changing text case Cell Cleaner can help you to convert numbers in the text format to the number format, delete unwanted characters and excess spaces in your Excel table. Download the free 30-day trial version and check out how useful the add-in can be for you.

Video: how to change case in Excel



I hope now that you know nice tricks for changing case in Excel this task will never be a problem. Excel functions, Microsoft Word, VBA macros or Ablebits add-in are always there for you. You have a little left to do - just choose the tool that will work best for you.

Table of contents

163 comments

  1. what is shortcut key of upper and lower and proper case

  2. very helpful article, thank you so much

  3. this is very helpful, thank you.

  4. Thanks a lot

  5. Thanks a lot, it's indeed helpful

  6. Proper is not working for me.

  7. Thank you very much for helping me and saving my time.

  8. Thanks you saved my lot of times to change in uppercase. I'm working on copy paste work. & your article is good for me thanks again. :)

  9. Hi Sir/Madam

    I want to know formula in excel for make short letter from sentence.
    Example A1= Roumdul White Rice, the result =RWR

    Please help me as soon as possible.

    thank

    • Hi Sam,

      the logic for your case is to find all the "spaces" and then convert the next character to uppercase and add them all into one string. There are a couple of challenges with this. First, how many spaces will be there in each of the text? Excel does not have a looping function to keep running for several times. So the number of spaces should be definable. Second, there should not be any cases where you would want a different logic of shortening. For example, if there are two cases where the characters are same RWR, you might want to adjust one of them to differentiate. This would call for AI.

      The second issue is hard to crack. But for the first one, we can solve it using the Text to column function to split the text at each space. Based on the number of spaces, your text would be sitting in multiple columns and you can enter the formula in an empty column to add all the first characters of the different columns.

      Taking the example you have provided, there would be three columns - A, B, C - where the text would be. Your formula will be in column D as "=upper(left(A1,1))&upper(left(B1,1))&upper(left(C1,1))"

      If your text has 4 words (4 spaces), it will be spread in 4 columns (A, B, C, D) and you have to add one more &upper(left(D1,1)) to the formula. The good thing about this formula is that it will work with text that has lesser words as well.

      Hope this helps

  10. This is a very quick guide and was very helpful.. the short cut (Shift + F3) didn't work for me.. i use Excel 2010. i had to make use of the formular.

    thanks.

  11. Thank you very much! Its save a lot of time for me

  12. wonderful, this methods are simply
    extraodinary, it have saved me lots and lots of stress keep it up.

  13. Thank's for given me lot of Excel information.

  14. PETER SMITH = Peter Smith
    JOHN MCTEAR = John Mctear
    But I need a correction to read
    JOHN MCTEAR = John McTear

    Excellent article but how would you solve the above.

    • I don't think there is any way to solve the issue. This is something that needs (extremely basic) AI logic. For example, if "Mc" is the only case where this applies, you can add a formula to catch that. Its complicated, but the logic is - 1. Find the position of the text "Mc" ---- 2. Change to proper case the text until "Mc" ---- 3. Change to proper case the rest of the characters.

      In your case, the position of Mc is 7 (step 1). So characters until Mc (7+1) will become John Mc when using proper case (step 2). then the characters after that when changed to proper case will become "Tear" (step 3) - Combine all three, you get John McTear.

      See the below formula - the "Find" function returns an error if the text (Mc) is not present. Hence the entire formula is packed inside an iferror function. Obviously, A1 is the cell where the original name is.

      =IFERROR(LEFT(PROPER(A1),FIND("MC",UPPER(A1))+1)&PROPER(RIGHT(A1,LEN(A1)-FIND("MC",UPPER(A1))-1)),PROPER(A1))

      Hope this helps.

  15. I was reading through your site and found that you give very good advice, so I wanted to see if you could help me with a minor Excel 2013 issue...

    I use Excel 2013 and would like to force an input to a cell as a capital letter. For example, if someone were to put a small x in the box, I want it converted automatically to a capital X as soon as they exit that cell. I do not want it applied globally, only to certain cells within a worksheet. I'd rather not use a macro for this as many people disable macros as security risks and the "Upper" function only mirrors the contents of one cell into another cell in capital letters.

    Can you help?

    Thank you,

    Chris

  16. Thank you for share this valuable information to us

  17. Thank you so much. What a time saver.

  18. hank you so much, for excel tips.

  19. Thank you so much, for excel tips.

  20. Thank you very much.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)