A capital idea: Changing case in Excel (video)

People often ask how to change the case of text in Excel. This issue tends to crop up when you work with data from external sources.

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

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

Technique 1: Use a function to change case

Functions are predefined formulas in Excel that are designed to do something useful for you. There are three that can help you change case: UPPER, LOWER, and PROPER. The 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, it will convert Jean-Paul SARTRE to Jean-Paul Sartre.

The basic steps for changing the case of text by using a function are as follows:

1. Insert a new column (such as B) next to the original column (such as A) that contains the text you want to convert.
2. Add a formula that will transform the data at the top of the new column (B). For example, you might type =LOWER(A2) in cell B2.
3. 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!)
4. Select the values in the new column (B), copy them, and then paste as values over the values in the original column (A).
5. Remove the new column (B), since it's no longer needed.

This sounds more complicated than it actually is. If you're confused, watch Bill's demo, which clarifies it all:

Technique 2: Use a macro to change case

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

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

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

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

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

For more great tips, visit Bill's site, MrExcel.com. Also, if you're new to VBA and want to explore the topic, I highly recommend Getting started with VBA in Excel 2010.

 -- Anneliese Wirth