Results 1 to 6 of 6
  1. #1
    simkins_michael
    Guest

    Change all Text to PROPER format in a column

    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).[img]/w3timages/icons/tonguea.gif[/img]

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Change all Text to PROPER format in a column

    use =proper() in a separate column, paste resulting values over source column, delete =proper() functions.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change all Text to PROPER format in a column

    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

    but change the "<font color=blue>Upper(Cell.Value)</font color=blue>" function to "<font color=blue>Application.Proper(Cell.Value)</font color=blue>"

    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

    ****

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Roanoke area, Virginia, USA
    Posts
    3,729
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change all Text to PROPER format in a column

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

    Range("A1") = StrConv(Range("A1"), vbProperCase)

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change all Text to PROPER format in a column

    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:

    <pre>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
    </pre>


    HTH

  6. #6
    simkins_michael
    Guest

    Re: Change all Text to PROPER format in a column

    Thank you all for responding. Lots of great ideas and I was able to convert my text data.[img]/w3timages/icons/laugh.gif[/img]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •