Results 1 to 7 of 7
  1. #1
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Converting upper case text (Excel 2000)

    I have a large spreadsheet with a column of descriptions (text). Some cells are upper & lower case (1st letter upper case the rest lower). Many are all upper case. I need to convert the all caps cells to 1st letter upper case the rest lower.
    Any help would be appreciated.

    Regards,

    Skip
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Converting upper case text (Excel 2000)

    Insert an empty column next to the column with descriptions (if necessary). Let's say that the descriptions are in column A, starting with cell A2. Enter the following formula in cell B2:
    =UPPER(LEFT(A2,1))&LOWER(RIGHT(A2,LEN(A2)-1))
    and fill down as far as needed.

    If you wish to keep the formulas, you can hide the original column (A in this example). If you wish to replace the original text, copy column B, then select column A, and Edit | Paste Special..., values option. You can delete or clear column B after that.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts

    Re: Converting upper case text (Excel 2000)

    if there is only one word you could also use
    =PROPER(A2)

    zeddy

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting upper case text (Excel 2000)

    Select the cells to be changed, then run this macro:

    Sub Proper()

    For Each cel In Selection
    gg = cel.Value
    cel.Value = "=Proper(""" & gg & """)"
    Next cel
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False

    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting upper case text (Excel 2000)

    I use the case convert utility at http://www.cpearson.com/excel/download.htm to solve these types of problems.
    It is not fast on large lists but it will do the job.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Converting upper case text (Excel 2000)

    This is much easier, it invloves no cutting/pasting and no formulas. When run, the contents of each cell in the selection are converted to Proper: the first letter of each word is Upper case all others are lowercase.

    Steve
    <pre>Option Explicit
    Sub SelectionProper()
    Dim rCell As Range
    For Each rCell In Selection
    rCell.Value = Application.WorksheetFunction. _
    Proper(rCell.Value)
    Next rCell
    End Sub</pre>


  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting upper case text (Excel 2000)

    Much more elegant. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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