All Caps needing changing

[Joe]

I have a worksheet that was written in all caps.
I want to change it to 1st letter caps and the rest small
case.
Does anybody know a quick way to do this?

 

[Ron de Bruin]

Hi Joe

Check out David McRitchie his site
http://www.mvps.org/dmcritchie/excel/proper.htm

This macro you can use for the selection

Sub Propercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = StrConv(cel.Value, vbProperCase)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Norman Harker]

Hi Joe!

See:

Chip Pearson:
http://www.cpearson.com/excel/case.htm

Chip has code for converting existing text to Proper case:

Sub ConvertToUpperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbProperCase)
End If
Next Rng
End Sub

But there's a lot more on the website and the topic page is well worth
bookmarking.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.