How do I apply the formula =PROPER(text) to existing text in works

[Guest]

I have a large worksheet with a lot of text that is in ALL CAPS. I need to
convert all of the text from ALL CAPS into Title Case (1st letter of each
work is capital, other letters are lower case). I am able to use this
formula:
=PROPER("text")
to change individual cells to Title Case, but I can't figure out how to do
the entire worksheet, or an entire column all at one time.

 

[Peo Sjoblom]

There are several ways, you could use a macro like this

Sub PropCase()
Application.DisplayAlerts = False
Dim R As Range
For Each R In Selection.Cells
If R.HasFormula Then
R.Formula = "=PROPER(" & Mid(R.Formula, 2) & ")"
Else
R.Value = Application.Proper(R.Value)
End If
Next
Application.DisplayAlerts = True
End Sub


press Alt + F11, click insert>module and paste in the above.
press Alt + Q to close the VBE, select the range in question and press Alt +
F8, double click the macro name

Or you could use the formula in an empty column, copy down/across as long as
needed, while selected copy it,
then do edit>paste special as values in place, then delete the original data

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)