+ Reply to Thread
Results 1 to 5 of 5

Auto change column to Title Case

  1. #1
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Smile Auto change column to Title Case

    Hi Guys,

    I have to swap data from one spreadsheet and paste that information into my own spreadsheet. The problem is the surnames in the source document are in block capitals. I was wondering if there was a vba code that I could use to change this to Title Case once I activated it.

    The surnames are stored in column B from Rows 7 to 100, it is possible for the macro to check only for the lines that are not blank as I don't need it checking every cell between B7 to B100 each and every time I run it? Although even if it does have to check the range B7 to B100 I guess it wouldn't take that long anyway?

    Thanks in advance.
    Best Regards.

    Michael
    -----------------------------------
    Windows Vista, Microsoft Office 2007

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    Not quite sure what you mean by 'Title case', but if you mean keeping the first character a capital, then subsequent characters to lower case, just use the =PROPER() function.

    HTH

  3. #3
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Thumbs up Yes I meant Proper Case

    Hi Richard Buttrey,

    Yes sorry I do mean Proper Case. I am aware of the function that you gave here, I just wanted to know if there was a way that I could quickly change the whole range in VBA once I pasted the data?

    Thanks in advance.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    You could get the macro to insert the =PROPER() function in a spare 'helper' column on the sheet and copy it down as many rows as necessary. Then simply copy this column and paste special values back to the original capitalised column.

    HTH

  5. #5
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Smile

    @Richard Buttrey

    Thanks for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Spellnumber
    By Williams in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 02-16-2020, 03:34 AM
  2. macro (FOR loop and IF's)
    By matino in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-05-2008, 09:10 AM
  3. Looking for a macro that copies a formula from column to column as I change the month
    By caliskier in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-18-2007, 03:32 PM
  4. change column heading into a drop down box
    By FNG in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-21-2007, 05:50 PM
  5. type mismatch
    By Mikeyhend in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2006, 01:17 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1