Formatting existing sheet

[Craig Arnott]

Hi All,
I have a spreadsheet full of contacts and addresses. At
the moment it is all in upper case. I want to convert the
whole sheet so that it is in the proper case of the first
letter upper and the rest lower case.

Does anyone know if this is possible

thanks in advance

Craig

 

[Andy B]

Craig

Here is a macro that I use. Simply select the range concerned and run the
macro. It isn't a macro I've created, but it was posted onto this NG a while
ago.

Sub TextConvert()
'code by Ivan F. Moala
Dim ocell As Range, ans As String

ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence ")
If ans = "" Then Exit Sub

For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & LCase(Right(ocell.Text,
Len(ocell.Text) - 1))
End Select
Next

End Sub


Andy.

 

[Andy B]

Craig.

Having looked at the macro, it doesn't offer the Proper function, only
Upper, Lower and Sentence. You may have to use helper columns and the
PROPER() function to get what you want.

Andy.

 

[Peo Sjoblom]

You would need a macro for that, press alt + F11 and click insert module
and paste in

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 + Q to close the VBE, select your data and press alt + F8 and run
the macro