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

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?

codeLes

Posted 2009-09-01T15:31:47.230

Reputation: 1 772

Answers

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

raven

Posted 2009-09-01T15:31:47.230

Reputation: 5 135

I had considered macros, but the solution is for a non programmer and I was wondering if there was a point a click solution... – codeLes – 2009-09-01T15:58:43.650

@codeLes: As far as I know, Excel provides no easy way to do this. A glaring omission if you ask me. – raven – 2009-09-01T16:05:39.250

@raven: thanks, add that to your answer as I'll update the question and this is my accepted answer. – codeLes – 2009-09-01T16:15:36.563

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.

harrymc

Posted 2009-09-01T15:31:47.230

Reputation: 306 093

In Word, shift-F3 will toggle between lowercase, UPPERCASE, and Title Case. – ale – 2009-09-01T20:12:23.980

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.

caliban

Posted 2009-09-01T15:31:47.230

Reputation: 18 979

cool, thanks for that tip, but I'm sure if the restrictions placed on the users will allow the use of a 3rd party plugin, but it's nice to know about. – codeLes – 2009-09-01T16:48:58.930

It's another long workaround - get a capable text editor (such as Notepad++). Copy the column of data you want to change the case for, and paste them into the text editor, change the case, and paste it back. Clumsy, but it's another solution all the same. – caliban – 2009-09-01T16:50:50.113

had considered that as well, and that's workable, but I wanted to know if excel could do it (2007 excel) – codeLes – 2009-09-01T17:36:16.293

Or do like I do, and switch to OpenOffice:

Step 1:

enter image description here