skip to Main Content
Changing Case

Changing Case

Have you ever had a list where EVERYTHING WAS ACCIDENTALLY ENTERED IN UPPER CASE? oR wORsE It WaS All miXed uP. Of course, you want to fix it, but what is the fastest way to do it? In this blog post we will show you how to do it quickly in Microsoft Word, PowerPoint and Excel.

The slow way:

The slowest way to change all the text to the desired case is to erase it all, and then retype it. That is a pain and depending on the number of entries in your list, it’s a big waste of time.

A faster way:

Word and PowerPoint

If you are using Microsoft Word or PowerPoint, a faster way is to use the ribbon. Simply highlight the text that needs to be changed, and then on the ribbon’s Home tab click the Change Case drop down menu shown below. From there you can select the desired case, and the computer will fix it for you.

Excel

Out of the box, Microsoft Excel does not have a menu option for changing case. However, you can use any of the following text functions to calculate the desired case:

UPPER() – makes the text all upper case
LOWER() – makes the text all lower case
PROPER() – capitalizes the first letter of each word

So, for example, if you have a mixed case list of presidents in column A, and you want it to be UPPER CASE you would…

First, in cell B2 enter the formula: =UPPER(A2)
Second, double click the Autofill corner of cell B2 to copy the formula down the column for each name.


Third, select and copy (Ctrl C) all the formula cells in column B.
Fourth, click on cell A2.
Fifth, from the ribbon’s Home tab, click the down arrow under Paste, and select the Values option.


Sixth, on the keyboard press the Esc key to get out of the cut/copy mode.
Finally, delete column B – right click column B and choose Delete.

The fastest way:

Word and PowerPoint

In Microsoft Word and PowerPoint, the fastest way to change the case of a text selection is with a keyboard shortcut. Simply select the desired text and the press Shift F3. This will toggle the text between Proper Case, UPPER CASE, and lower case.

Excel

In Microsoft Excel, the fastest way to change the case of text is with the following Excel Macro.

Sub MakeProperCase()

‘Excel macro created by TechMentors – www.theTechMentors.com
‘With one exception, this macro converts all the cells in the selected range to Proper Case.
‘Example 1: george washington becomes George Washington.
‘Example 2: JOHN ADMADS becomes John Adams.
‘Example 3: thOmAS JEffERSoN becomes Thomas Jefferson.
‘The one exception is this macro will not convert cells with formulas.

‘Create a variable that represents a range/cell.
Dim c As Range

‘Create a loop for each cell in the selected area.
For Each c In Selection

‘If the cell is NOT the result of a formula then….
If Not c.HasFormula Then

‘Change the cell’s value into its Proper Case equivalent.
c.Value = Application.WorksheetFunction.Proper(c.Value)

End If

‘Repeat the process for the next cell in the selection.
Next c

End Sub

Once this macro has been put in your Personal Macro Workbook you can simply select the desired text and then run the macro.

You and your team can learn more about creating macros by taking TechMentors’ Excel Macros and VBA course. You can find it any many other courses on our training services page.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top