Results 1 to 8 of 8

Change all caps to lower case except the first letter.

This is a discussion on Change all caps to lower case except the first letter. within the Excel Questions forums, part of the Question Forums category; Hi, How do I change everything in my B column from all caps to lower case except the first letter ...

  1. #1
    Board Regular
    Join Date
    Dec 2010
    Posts
    53

    Default Change all caps to lower case except the first letter.

    Hi,

    How do I change everything in my B column from all caps to lower case except the first letter of each word. The B column contains VLOOKUP formula also. Thanks.

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,520

    Default Re: Change all caps to lower case except the first letter.

    Try

    =PROPER(B1)
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    6,307

    Default Re: Change all caps to lower case except the first letter.

    =left(b2)&lower(right(b2,len(b2)-1))

    EDIT - I thought you only wanted to capitalise the first letter in the cell. For the first of each word, use VOG's suggestions.
    Neil

  4. #4
    Board Regular
    Join Date
    Dec 2010
    Posts
    53

    Default Re: Change all caps to lower case except the first letter.

    Hi All,

    Column B is containing VLOOKUP formula so I can't put anything in the cell.

  5. #5
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,520

    Default Re: Change all caps to lower case except the first letter.

    If this is the same lookup as earlier, try changing it to

    =IF(ISNA(VLOOKUP(F3,'Device List'!E2:F814,2,FALSE)),"",PROPER(VLOOKUP(F3,'Device List'!E2:F814,2,FALSE)))
    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    Board Regular
    Join Date
    Dec 2010
    Posts
    53

    Default Re: Change all caps to lower case except the first letter.

    Is there a way I can change entire column that have info in the cells already?

  7. #7
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,520

    Default Re: Change all caps to lower case except the first letter.

    Note that this will replace formulas with values.

    Try selecting the range then running the macro

    Code:
    Sub ToProper()
    Dim c As Range
    For Each c In Selection
        c.Value = StrConv(c.Value, vbProperCase)
    Next c
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  8. #8
    Board Regular
    Join Date
    Dec 2010
    Posts
    53

    Default Re: Change all caps to lower case except the first letter.

    Thanks VoG

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com