3 Ways to Change Character Case in Excel 2013, 2010 and 2007

In this article, I want to tell you about several ways to change the case of characters in Excel from upper to lower or how to capitalize each word. You will learn how to deal with such tasks with the help of functions REGULATORY и LOWER, using VBA macros, and also using Microsoft Word.

The problem is that Excel does not provide a special tool for changing the case of text on a worksheet. It remains a mystery why Microsoft gave Word such a powerful feature and didn’t add it to Excel. This would make many tasks easier for most users. But do not rush to frantically retype all the text data of your table manually! Luckily, there are some good ways to convert text values ​​in cells to upper or lower case, or capitalize each word. Let me share with you these ways.

• Excel functions to change text case
• Change register text using Microsoft Word
• Changeable case using VBA macro

Excel functions to change text case

Microsoft Excel has three great features that you can use to change the case of text. it UPPER (REGISTERED), LOWER (LOWER) and PROPER (PROPANACH).

• Function UPPER (UPPER) converts all lowercase characters to uppercase.
• Function LOWER (LOWER) makes all uppercase letters lowercase.
• Function PROVIA (PROPER) capitalizes the first letter of each word and lowercases the rest.

All three of these functions work in the same way, so I’ll show you how one of them works. Let’s take the function as an example UPPER (REGISTERED):

Entering a formula in Excel

0. Insert a new (helper) column next to the one containing the text you want to convert.

Note: This step is optional. If the table is not large, you can simply use any adjacent empty column.

0. 
1. Enter an equal sign (=) and a function name UPPER (UPPER) to the adjacent cell of the new column (B3).
2. In brackets after the function name, enter the appropriate cell reference (C3). Your formula should look like this:

   =UPPER(C3)

   =ПРОПИСН(C3)

   where C3 is the cell with the text to be converted.

   

3. Press Enter.The figure above shows that in the cell B3 contains the same text as in C3, in capital letters only.

Copy the formula down the column

Now you need to copy the formula to the rest of the auxiliary column cells:

4. Select the cell with the formula.
5. Hover your mouse pointer over the small square (autofill marker) in the lower right corner of the selected cell so that the pointer turns into a small black cross.
6. Click and hold down the left mouse button and drag the formula down through all the cells where you want to copy it.
7. Release the mouse button.

Note: If you need to completely fill a new column (to the full height of the table), then you can skip steps 5-7 and just double-click on the autofill marker.

Removing an auxiliary column

So, you have two columns with the same text data, differing only in case. I assume that you want to leave the column with only the desired option. Let’s copy the values ​​from the helper column and get rid of it.

8. Select the cells containing the formula and click Ctrl + Cto copy them.
9. Right-click on the first cell in the original column.
10. In the context menu under Paste Options (Paste Options) select Values (Values).Since we only need text values, we will choose this option to avoid errors in formulas in the future.
11. Right-click on any cell of the auxiliary column and select the command from the context menu Delete (Delete).
12. In the dialog box Delete (Delete Cells) select an option Entire column (Column) and click OK.

Done!

In theory, this may seem too complicated. Relax and try all these steps on your own. You will see that changing case with Excel functions is not difficult at all.

Change the case of text in Excel using Microsoft Word

If you don’t want to mess around with formulas in Excel, you can change case in Word. Here’s how this method works:

1. Select the range on the Excel worksheet in which you want to change the text case.
2. Press Ctrl + C or right-click and select the command from the context menu Copy (Copy).
3. Create a new Word document.
4. Press Ctrl + V or right-click on a blank page and select the command from the context menu paste (Insert). The Excel table will be copied to Word.
5. Select the text you want to change case.
6. On the Advanced tab Home (Home) in the section Font (Font) click icon Change Case (Register).
7. Select one of the 5 case options from the drop-down list.

Note: In addition, you can press the combination Shift + F3until the desired style is set. With these keys, you can select only upper and lower case, as well as case as in sentences.

Now you have a table in Word with the text case changed. Just copy it and paste it in its original place in Excel.

Change text case with VBA macro

You can also use VBA macros in Excel 2010 and 2013. Don’t worry if your VBA knowledge leaves much to be desired. I didn’t know much about this either a while ago, and now I can share three simple macros that change the case of text to uppercase, lowercase, or capitalize each word.

I will not digress from the topic and tell you how to insert and run VBA code in Excel, as this is wonderfully described in other articles on our site. I’ll just show macros that you can copy and paste into your book.

• If you want to convert text to uppercase, use the following 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 lowercase to your data, use the code shown below:

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

• Here is a macro that will make all words in the text start with a capital letter:

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

I hope that now that you know a couple of great tricks for changing case in Excel, this task will be easy for you. Excel functions, Microsoft Word, VBA macros are always at your service. There is very little left for you to do – decide which of these tools you like best.