Changing text from all caps to proper

[beland]

I need to change the text in a worksheet from ALL CAPS to proper. Please help.

Thank you.

 

[dk]

Two methods:-

Use the =PROPER(A1) worksheet function to convert it on a cell by cell basis.

Use this macro to do it for you:-

Sub ConvertToProper()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange.Cells
        c.Formula = WorksheetFunction.Proper(c.Formula)
    Next c
End Sub

 

[JugglerJAF]

Use the PROPER function

In a spare cell, type in the formula:
=PROPER(A1)

(of course use whatever cell reference is appropriate) and copy the formula down.

BEWARE: If you are converting names from upper case to capitalised, the proper function does not do a good job with names like MCDOUGALL, changing it to Mcdougall instead of to McDougall. Remember, the PROPER function will only capitalise the first letter of each word!

 

[maxflia10]

http://www.sasp-utilities.com

has a function to start first word in uppercase or start each word in upper case.

Or an Excel function =proper(a1)

 

[Richie(UK)]

Hi,

You might also want to consider this versatile little piece of code (one of Ivan's I think):

<pre>'text converter
Sub TextCon()
Dim ocell As Range, ans As String

ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")

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))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub</pre>
HTH