Swap from all Caps

[Mark]

I have a spread sheet that is all caps. I would like to
have the first letter stay Cap then the rest of each word
small. Is there a way,formula or macro to do this easily?
Thanks in advance for your help.

 

[NH]

=Proper(A1) will make all first characters of a word Upper case
or
=Upper(left(a1))&lower(mid(a1,2,999))

 

[Gord Dibben]

Mark

Macro code.

Sub Proper_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 = Application.Proper(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

See David's website for more on Proper, Upper and Lower case.

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

Gord Dibben Excel MVP