How do I convert a column of text to upper case in Excel 2007 without creating a new column?

Question

I know that you can use formulas and convert the data that way, but how can I convert to Upper (or Lower or proper) in place? Is there an easy way built into excel to instruct a non-superuser with?

Answer 1

As far as I know, Excel provides no easy way to do this. A glaring omission if you ask me. You can do it with a VBA macro:

Sub ConvertToUpperCase()
    Dim Rng As Range
    On Error Resume Next
    Err.Clear
    Application.EnableEvents = False
    For Each Rng In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
        If Err.Number = 0 Then
           Rng.Value = StrConv(Rng.Text, vbUpperCase)
        End If
    Next Rng
    Application.EnableEvents = True
End Sub

Answer 2

If the user is amenable to installing add-ins, ASAP utilities has a menu function to convert all selected cells to the desired case.

ASAP Utilities

It should be quite simple for the end-user. Install ASAP Utilities, restart Excel, and the menu function is now available.

Answer 3

If the user can use Word in addition to Excel, this might work:

• Select the column in Excel and Copy.
• Open word and Paste.
• Select the column in Word and use Format/Change Case with lowercase or UPPERCASE.
• Select the Word column and Paste Special to replace the text in Excel.

Answer 4

Or do like I do, and switch to OpenOffice:

Step 1:

Step 2:

Step 3:

I was frustrated with MS Office when it didn't even had the simplest of simple functions available and only through macro then..