proper case a a whole range
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: proper case a a whole range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2017
    Posts
    459
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default proper case a a whole range

    hi.

    data in the range i3:i8000

    is there away to force the data alredy in the above range to lowercase ?(i have put some code in so future any data entered will do this)... is there a way to do this without having to change each line manually?

    MTIA
    Thank you.
    Orbis non sufficit

    Trevor3007

  2. #2
    Board Regular
    Join Date
    Apr 2019
    Posts
    72
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: proper case a a whole range

    Code:
    Sub MakeItSmall()
    Dim cell As Range
    For Each cell In [I3:I8000]
        cell.Value = LCase(cell.Value)
    Next cell
    End Sub
    Last edited by KOKOSEK; Jun 18th, 2019 at 08:38 AM.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,665
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: proper case a a whole range

    Another option
    Code:
    Sub Trevor3007()
       With Range("I3", Range("I" & Rows.Count).End(xlUp))
          .Value = Evaluate(Replace("if(@="""","""",proper(@))", "@", .Address))
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,984
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: proper case a a whole range

    Quote Originally Posted by Fluff View Post
    Another option
    Code:
    Sub Trevor3007()
       With Range("I3", Range("I" & Rows.Count).End(xlUp))
          .Value = Evaluate(Replace("if(@="""","""",LOWER(@))", "@", .Address))
       End With
    End Sub
    I know the thread title say "Proper Case", but the OP's description says he wants "Lower Case".
    Last edited by Rick Rothstein; Jun 18th, 2019 at 12:23 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    Jan 2017
    Posts
    459
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: proper case a a whole range

    Quote Originally Posted by Fluff View Post
    Another option
    Code:
    Sub Trevor3007()
       With Range("I3", Range("I" & Rows.Count).End(xlUp))
          .Value = Evaluate(Replace("if(@="""","""",proper(@))", "@", .Address))
       End With
    End Sub
    Hi Fluff,

    thanks for the VB...works a treat many thanks.
    Thank you.
    Orbis non sufficit

    Trevor3007

  6. #6
    Board Regular
    Join Date
    Jan 2017
    Posts
    459
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: proper case a a whole range

    Quote Originally Posted by KOKOSEK View Post
    Code:
    Sub MakeItSmall()
    Dim cell As Range
    For Each cell In [I3:I8000]
        cell.Value = LCase(cell.Value)
    Next cell
    End Sub
    hello KOKOSEK ,

    thank you for your reply. I am unsure why you sent VB for 'lower' as i requested VB for Proper Case, but many thanks anyhoo...i am sure i willl need & use soon.
    Thank you.
    Orbis non sufficit

    Trevor3007

  7. #7
    Board Regular
    Join Date
    Jan 2017
    Posts
    459
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: proper case a a whole range

    Quote Originally Posted by Rick Rothstein View Post
    I know the thread title say "Proper Case", but the OP's description says he wants "Lower Case".

    good evening double R,

    thanks for your help & my OP should of been 'Proper Case' not lower. sorry my bad.
    Thank you.
    Orbis non sufficit

    Trevor3007

  8. #8
    Board Regular
    Join Date
    Jan 2017
    Posts
    459
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: proper case a a whole range

    Quote Originally Posted by KOKOSEK View Post
    Code:
    Sub MakeItSmall()
    Dim cell As Range
    For Each cell In [I3:I8000]
        cell.Value = LCase(cell.Value)
    Next cell
    End Sub
    hello,

    DOH... now I know why the 'lower' VB. my OP stated this...my bad
    Thank you.
    Orbis non sufficit

    Trevor3007

  9. #9
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,862
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: proper case a a whole range

    An another means:

    Load your data into Power Query. Select All Columns. Select Transform. Select Format. Select Lowercase. Close and Load.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,665
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: proper case a a whole range

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •