Making text all CAPS in excel

[Jon]

Is it possible to make all lower-case letters in a given spreadsheet show up
in CAPS? I ask because I am working on a spreadsheet that containers
thousands of cells which have mixed and matched lower and upper-case letters,
and would like to have them all show upper-case for the sake of consistency.

Thank you,
Jon

 

[Bob Phillips]

With VBA

Dim cell
For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
cell.Value = UCase(cell.Value)
End If
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Paul B]

Jon, you will need a macro for that, like this

Sub Upper()
Dim Rng As Range
For Each Rng In ActiveSheet.UsedRange
Application.ScreenUpdating = False
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
Application.ScreenUpdating = True
End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Jon]

Thanks guys -

Bob I went with Paul's VB formula - the one you gave didn't work for some
reason? It works great and I've set up a quickkey so that I can use it in any
spreadsheet from this point on.

Thanks a bunch!

 

[Gord Dibben]

Bob's works for me, but not as posted.

Must be surrounded by Sub whatever() and End Sub lines.

Gord Dibben Excel MVP