Contents
Many Excel users experience difficulties due to the inability to quickly change the case of text in worksheets. For some reason, Microsoft only added this feature to Word and left Excel without it. But this does not mean that you need to manually change the text in each cell – there are several shorter ways. Three of them will be described below.
Excel special functions
In Excel, there are functions that display text in a different case – REGULATORY(), LOWER() и prop(). The first of them translates all the text into uppercase, the second – into lowercase, the third converts only the initial letters of words into uppercase, the rest leaves in lowercase. They all operate on the same principle, therefore, using one as an example – let it be REGULATORY() – you can see how to use all three.
Enter formula
- Create a new column next to the one you want to modify, or if it’s convenient, just use an empty column next to the table.
- Enter an equal sign (=) followed by a function name (REGULATORY) in the column cell next to the top-most of the editable text cells.
In brackets after the function name, write the name of the adjacent cell with text (in the screenshot below, this is cell C3). The formula will look like =PROPISN(C3).
- Hit Enter.
Cell B3 now contains the text of cell C3 in uppercase.
Copy the formula to the underlying cells of the column
Now the same formula can be applied to other cells in the column.
- Select the cell containing the formula.
- Move the cursor to the small square (fill marker), which is located at the bottom right of the cell – the cursor arrow should turn into a cross.
- Keeping the mouse button pressed, drag the cursor down to fill in all the required cells – the formula will be copied into them.
- Release the mouse button.
If you need to fill all the cells of the column to the bottom edge of the table, just hover over the fill marker and double-click.
Remove helper column
Now there are two columns with the same text in the cells, but in different case. To keep only one, copy the data from the helper column, paste it into the desired column, and delete the helper.
- Select the cells containing the formula and click Ctrl + C.
- Right-click on the first of the cells with the desired text in the editable column.
- Under “paste options” select the icon The values in the context menu.
- Right click on the helper column and select Remove.
- In the dialog box that appears, select Entire Column.
Now everything is done.
The explanation may seem complicated. But just follow the given steps and you will see that there is nothing difficult in it.
Editing the text using Microsoft Word
If you don’t want to mess around with formulas in Excel, you can use the command to change case in Word. Let’s see how it works.
- Select the cells you want to make changes to.
- The applications Ctrl + C or right-click on the selected area and select Copy in the context menu.
- Open a new document in Word.
- Press Ctrl + V or right-click the sheet and select Insert.
Now a copy of your table is in the Word document.
- Select those table cells where you want to change the case of the text.
- Click icon Регистр, which is located in the group Font in the tab Home.
- Select one of the five case options from the drop-down list.
You can also select text and apply Shift + F3 until the text is correct. In this way, you can choose only three case options – upper, lower and Sentence case (in which each sentence begins with an uppercase letter, the rest of the letters are lowercase).
Now that the text in the table is in the desired form, you can simply copy it back into Excel.
Applying VBA macros
For Excel 2010 and 2013, there is another way to change text options – VBA macros. How to insert VBA code into Excel and make it work is a topic for another article. Here, only ready-made macros that can be inserted will be shown.
You can use the following macro to convert text to upper case:
Sub Uppercase()
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = UCase(Cell.Value)
End If
Next Cell
End Sub
For lower case, this code will do:
Sub Lowercase()
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = LCase(Cell.Value)
End If
Next Cell
End Sub
Macro to make each word start with an uppercase 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
Now you know how you can change the case of text in Excel. As you can see, this is not that difficult, and there is not even one way to do it – which of the above methods is better is up to you.