Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 3 down vote accepted

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
share|improve this answer
    
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 Sep 1 '09 at 15:58
    
@codeLes: As far as I know, Excel provides no easy way to do this. A glaring omission if you ask me. –  raven Sep 1 '09 at 16:05
    
@raven: thanks, add that to your answer as I'll update the question and this is my accepted answer. –  codeLes Sep 1 '09 at 16:15

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.

share|improve this answer
    
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 Sep 1 '09 at 16:48
    
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 Sep 1 '09 at 16:50
    
had considered that as well, and that's workable, but I wanted to know if excel could do it (2007 excel) –  codeLes Sep 1 '09 at 17:36

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.
share|improve this answer
    
In Word, shift-F3 will toggle between lowercase, UPPERCASE, and Title Case. –  Al E. Sep 1 '09 at 20:12

Or do like I do, and switch to OpenOffice:

Step 1:

enter image description here

Step 2:

enter image description here

Step 3:

enter image description here

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.