Results 1 to 5 of 5

Thread: VBA Macro To Convert Text To Proper Case

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13

    VBA Macro To Convert Text To Proper Case

    I have data in Col E. I would like a macro to convert the text from row 2 onwards into proper case

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Code:
    Sub ConvertToProper()
    
        Dim lng As Long
        lng = Cells(Rows.Count, "E").End(xlUp).Row
        Range("E2:E" & lng).Value = Evaluate("=IF(E2:E" & lng & "<>"""",PROPER(E2:E" & lng & "),"""")")
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Thanks for the help, much appreciated

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Code:
    Sub ConvertToProper()
        Dim lng As Long
        lng = Cells(Rows.Count, "E").End(xlUp).Row
        Range("E2:E" & lng).Value = Evaluate("=IF(E2:E" & lng & "<>"""",PROPER(E2:E" & lng & "),"""")")
    End Sub
    You can shorten that Evaluate statement slightly by doing it this way...
    Code:
    Sub ConvertToProper()
      Dim lng As Long
      lng = Cells(Rows.Count, "E").End(xlUp).Row
      Range("E2:E" & lng).Value = Evaluate("IF(ROW(),PROPER(E2:E" & lng & "))")
    End Sub

  5. #5
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Hi Rick, thanks for your input-much appreciated

Similar Threads

  1. Macro to check values based on certain text
    By Howardc in forum Excel Help
    Replies: 25
    Last Post: 11-05-2012, 09:03 PM
  2. Need VBA code to convert csv to xlsx and vice versa
    By Pravee89 in forum Excel Help
    Replies: 1
    Last Post: 10-13-2012, 11:31 PM
  3. Reverse name in excel with upper case,edit formula
    By shrinivasmj in forum Excel Help
    Replies: 3
    Last Post: 09-11-2012, 01:31 PM
  4. Convert Text In YYYYMMDD Format To Date Format
    By S M C in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 02-28-2012, 12:04 AM
  5. Change Case of Text in Excel
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 08-11-2011, 08:33 AM

Posting Permissions

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