A capital idea: changing the case in Excel (video)

People often ask how to change case of text in Excel. This issue tends to arise when working with data from external sources.

Unlike Word, Excel does not have a keyboard shortcut or button Change case to magically switching between letters uppercase, lowercase or proper case. Now, this doesn't mean that you have to manually retype all your text. (In fact, the very thought of you that makes me cry.) Instead, you can use a function designed specifically for this work, or even a macro.

Excel MVP Bill Jelen has recently created a couple of videos that explain each of these techniques.

Functions are predefined formulas in Excel that are designed to do something useful for you. There are three which can help to change the case PROPER, top and bottom. Upper and lower functions are self-explanatory. PROPER capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. For example, convert Jean-Paul SARTRE Jean-Paul Sartre.

The basic steps for editing in the case of text using a function are as follows:

Insert a new column (for example B) next to the original column (for example) that contains the text that you want to convert.Add a formula that will transform the data in the upper part of the new column (B). You could, for example, type = LOWER (A2) in cell B2.Fill down the formula in the new column (B). In an Excel table, a calculated column is automatically created with values filled down. (Love that!)Select values for the new column (B) copy and then paste as values above the values in the original column (A).Remove the new column (B), since it is no longer necessary.

This sounds more complicated than it actually is. If you're confused, watch the demo to Bill, who explains everything:

The preceding technique works great if you are converting the text on an infrequent basis. If you need to do this more regularly, you can create a macro that changes the case and then create a toolbar button that launches the macro whenever you need it. In the video below, Bill you walk through the process of creating a macro, and then hooking up to a button on the Quick Access toolbar.

Here's the VBA macro code of the Bill, if you want to try this on their own.

Sub UpperSelection ()
For each cell in selection
If no cells.Then HasFormula Property
cell.Value = UCase (cell.Value)
End If
Next cell
End Sub

Sub LowerSelection ()
For each cell in selection
If no cells.Then HasFormula Property
cell.Value = LCase (cell.Value)
End If
Next cell
End Sub

Sub ProperSelection ()
For each cell in selection
If no cells.Then HasFormula Property
cell._Application = Value
.PROPER (cell.Value)
End If
Next cell
End Sub

For more tips, visit the MrExcel.com Web site Bill. Also, if you are new to VBA and desire to explore the theme, I recommend that getting started with VBA in Excel 2010.

-Anneliese Wirth

View the original article here