Column to Proper Case

[marka87uk]

I have some text which is in capitals filling a column. How can I loop though each cell of text and change it to proper case (or simply just change the whole column)?

 

[Smitty]

You can use =PROPER(A1) then Copy-->Paste Special-->Values or try:

Sub TextCon()
'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, (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

HTH,

Smitty

 

[marka87uk]

Thanks, that pointed me in the right direction. In the end I did this:

Range("F1").Select
Range(Selection, Selection.End(xlDown)).Select
Dim pCell As Range
For Each pCell In Selection
pCell.Value = Application.WorksheetFunction.Proper(pCell.Value)
Next pCell

Not sure if there is a simpler way but that works fine for me!