Change text

A

Andersos

Is it possible to change an enitre work sheets text to all caps? It is
not an option under font - effects....
 
S

starguy

suppose your data is in sheet1, put in cell A1 of sheet
=UPPER(sheet1!A1) and copy it across the rows and columns in which you
data is in sheet1.
this will display text in upper case (all caps).
if you want to copy formats from sheet1 as well then do a bit more.
in sheet2 press Ctrl+A to select the whole sheet, then press Ctrl+C t
copy the selection (dont deselect the sheet), right click on th
selection and select Paste Special and check the option values an
click Ok

in sheet1 press Ctrl+A (to select all sheet) then press Ctrl+C to cop
the sheet, come to sheet 2 and right click on cell A1 of sheet2 an
select Paste Special and check the option Formats and click Ok.

hopefully this would solve your problem
 
G

Gord Dibben

Personally I would find an all-caps worksheet to be hard to read and quite
jarring to the senses, but..................

You can use the worksheet function UPPER to change one cell at a time with
formulas or a macro to change entire sheet at once with no formulas.

Sub optUpper_Click()
'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
 
Top