+ Reply to Thread
Results 1 to 5 of 5

Clever way of formatting names nicely?

  1. #1
    Registered User
    Join Date
    07-24-2012
    Location
    Scotlland
    MS-Off Ver
    Excel 2003
    Posts
    9

    Clever way of formatting names nicely?

    I have a column with lots of names, and I want to format them nicely using VBA. I've been trying to use formulas in a spare cell using LEFT, RIGHT, MID, FIND, ISERROR, etc, to get the desired result, then copy/paste the resulting formula into my VBA code, but it's getting very unwieldy, long and hard to follow.

    Basically the names as it stands can be anything from:

    SMITH, JOHN M.
    JONES
    TAYLOR, CHRISTOPHER (Chris)
    BAKER, MIKE
    SMYTH, EDWARD E. (Ted)
    MARK.MILLER
    PAUL PARKER

    I want to format them so that they become First Initial. Surname, unless there's only one word, then I want it left as-is. And I need the whole thing converted to Proper case.

    So in the example above, the finished result should be:

    J Smith
    Jones
    C Taylor
    M Baker
    E Smyth
    M Miller
    P Parker

    Thanks!

    Also posted here:

    http://www.mrexcel.com/forum/subscri...php?folderid=0

    http://www.ozgrid.com/forum/showthread.php?t=197132
    Last edited by gers1978; 10-14-2015 at 08:37 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    560

    Re: Clever way of formatting names nicely?

    You need to define all the identifiers that will mark the end of a surname, etc. You then need to ensure that your data follows the same rules all the way through, eg:

    PAUL PARKER can be identified as being in firstname - surname format but are there any data entries that are PARKER PAUL format? If not then you can search for a space in the name and identify everything before the space as a surname and after a first name. Same with the full stop in the name, if the data only contains Firstname.Surname format then you can search for a .

    If your data doesnt follow these rules, ie you have surname firstname or surname.firstname or firstname, surname, etc.....then you're going to have serious problems identifying where a surname is located in the string.

  3. #3
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    560

    Re: Clever way of formatting names nicely?

    example code to identify where a surname stops if in 'surname, firstname' format and convert into correct case:

    Please Login or Register  to view this content.
    Last edited by BellyGas; 10-14-2015 at 08:59 AM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,049

    Re: Clever way of formatting names nicely?

    Deleted...........
    Attached Files Attached Files
    Last edited by jindon; 10-14-2015 at 07:47 AM. Reason: Attached locked file

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,049

    Re: Clever way of formatting names nicely?

    Please Login or Register  to view this content.
    Last edited by jindon; 10-15-2015 at 02:19 AM.

+ 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. [SOLVED] Formatting names by seperating last names from initials by a comma
    By Taisir in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-28-2014, 03:04 AM
  2. INDIRECT doesn't play nicely with external links...Help!
    By tbarclay in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-09-2014, 11:26 AM
  3. Replies: 2
    Last Post: 07-01-2013, 08:30 AM
  4. [SOLVED] The OR() function does not work nicely :(
    By zjianguk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2013, 09:32 AM
  5. Two procedures not playing nicely together
    By Scott N in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-26-2013, 09:07 AM
  6. Replies: 2
    Last Post: 12-01-2011, 09:19 AM
  7. [SOLVED] How to later and earlier versions of Excel to play nicely?
    By plh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-24-2006, 02:25 AM

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