how do you cap all letters on a spread sheet in excel ?

Excel has an UPPER Function.

Assuming Column A with lower case text.

In B1 enter = UPPER(A1)

Drag/copy down. You have to do this for each column of text.

Method 2 involves a VBA macro which converts the text in-cell without using a
formula and can cover the entire sheet at a go.

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

On Sat, 3 Feb 2007 11:57:00 -0800, ross wrote:

how do you cap all letters on a spread sheet in excel ?