Change all Text to PROPER format in a column

Topic

New Reply

Does anyone have a MACRO or simple solution to change all UPPERCASE text in a cell into Proper format (1st letter of word is capatalized and rest are lower case).

Reply | Quote

Replies

use =proper() in a separate column, paste resulting values over source column, delete =proper() functions.

Reply | Quote

If you are doing this on a one-time basis, John’s suggestion is th way to go. If you are doing this often, try the code in
http://www.wopr.com/cgi-bin/w3t/showthread…d&sb=5#Post1143%5B/url%5D

but change the “Upper(Cell.Value)” function to “Application.Proper(Cell.Value)”

This will convert the selected cells from upper- or mixed-case to proper case. That thread is not a bad place to start looking at VBA macros for this sort of thing. If you are having a hard time linking to that post, the whole code would like like this:

****

Sub ConvertToUpper()
On Error GoTo errConvertToUpper
Dim Cell As Range
For Each Cell In Selection
If Not Cell.HasFormula Then Cell.Value = Application.Proper(Cell.Value)
Next Cell
exitConvertToUpper:
Exit Sub
errConvertToUpper:
If Err.Number = 438 Then
MsgBox “You probably don’t have cell(s) selected”, vbExclamation, “Selection Alert”
Resume exitConvertToUpper
End If
MsgBox Err.Number & ” ” & Err.Description
Resume exitConvertToUpper
End Sub

****

Reply | Quote

You need to use StrConv and vbPropercase. Examples should be in help, but here is a quickie example.

Range(“A1”) = StrConv(Range(“A1”), vbProperCase)

Reply | Quote

I provided some code in the VBA forum for UPPER, however since you asked here, I’ll provide my version of dcardno’s answer. (I prefer to use the VBA function StrConv, since there’s no VBA Proper like there is UCase and LCase)

How about this:

Sub ConvertToProper()
    On Error GoTo errConvertToProper
    Dim Cell As Range
    For Each Cell In Selection
        If Not Cell.HasFormula Then Cell.Value = StrConv(Cell.Value, vbProperCase)
    Next Cell
exitConvertToProper:
    Exit Sub
errConvertToProper:
    If Err.Number = 438 Then
        MsgBox "You probably don't have cell(s) selected", vbExclamation, "Selection Alert"
        Resume exitConvertToProper
    End If
    MsgBox Err.Number & " " & Err.Description
    Resume exitConvertToProper
End Sub

HTH

Reply | Quote

Thank you all for responding. Lots of great ideas and I was able to convert my text data.

Reply | Quote