changing case

[kmkx70a]

Somehow I'm having trouble changing case in a spreadsheet. I brought up help
but I'm missing something. I want to change everything to upper case. Help

 

[Paul Hyett]

In microsoft.public.excel on Fri, 21 Sep 2007, kmkx70a

Somehow I'm having trouble changing case in a spreadsheet. I brought up help
but I'm missing something. I want to change everything to upper case. Help

I think the function you need is UPPER()

 

[Trevor Shuttleworth]

I don't think you can easily do it in place. There isn't a format to change
to Upper Case ... unless it's been introduced in Excel 2007

You could use the UPPER function to change cells and then Copy |
PasteSpecial | Values over the original. If there's a lot of cells /
columns / rows, it might be easier to set up a temporary sheet and link back
to the cells on the original sheet. This can be messy because blank cells
will show as zero (0).

The danger of Copy | PasteSpecial | Values is that you'd lose any formulae.

More sophisticated but less straightforward is to use VBA to test each cell
and check for text values and convert them individually.

Regards

Trevor

 

[Gord Dibben]

Trevor alluded to VBA to change the entire sheet although UPPER CASE is hard to
read so I would personally not format in that manner but here it is from David
McRitchie.


Sub Upper_Case()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = UCase(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[David McRitchie]

Trevor alluded to VBA to change the entire sheet although UPPER CASE is hard to
read so I would personally not format in that manner but here it is from David
McRitchie. [code snipped]

The webpage containing the macro explains the code used, and may be of
help in writing macros that run faster.
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm

 

[Gord Dibben]

Color me red.

I should have posted the URL so OP could have received more info.

Happy you caught this David.

Gord

Trevor alluded to VBA to change the entire sheet although UPPER CASE is hard to
read so I would personally not format in that manner but here it is from David
McRitchie. [code snipped]

The webpage containing the macro explains the code used, and may be of
help in writing macros that run faster.
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm

 

[David McRitchie]

Hi Gord,
Thanks, I've reworked the HTML a bit to make the title more oriented
to usage rather than content, and changed the HTLM fragment-id
(location identifier) to #ucase_all , and some heading levels, so
it looks a lot better now in a Document Map of Headers.

Capitalize Cells with Formulas and/or Constants (#ucase_all)
http://www.mvps.org/dmcritchie/excel/proper.htm#ucase_all

instead of "Process cell with Formulas and/or Constants (#nonblanks)"

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

Color me red.

I should have posted the URL so OP could have received more info.

Happy you caught this David.

Gord

Trevor alluded to VBA to change the entire sheet although UPPER CASE is hard to
read so I would personally not format in that manner but here it is from David
McRitchie. [code snipped]

The webpage containing the macro explains the code used, and may be of
help in writing macros that run faster.
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm