Results 1 to 6 of 6
Like Tree2Likes
  • 1 Post By Rick Rothstein
  • 1 Post By snb

Thread: Reversing a "First Middle Last" Name to "Last, First Middle" Name Format

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    548
    Rep Power
    3

    Reversing a "First Middle Last" Name to "Last, First Middle" Name Format

    This just came up (again, for the thousandth time maybe) and I came up with a different formula than I had in the past. This formula works correctly for the following name layouts...

    First Last

    First Middle Last

    First M Last

    First M. Last

    First1 First2 Last

    First1 First2 Last1 Last2

    That next-to-last one being a name like "Mary Ann Jones" where "Mary Ann" is the full first name. The last one, on the other hand, covers a name like "Mary Ann Della Rossa" where "Mary Ann" is the full first name and "Della Rossa" is the full last name. I should note that the last one could be thought of as this also...

    First Middle Last1 Last2

    First M Last1 Last2

    First M. Last1 Last2

    which, of course, are also covered by the formula. Unfortunately, this formula will not correctly handle this arrangement...

    First Last1 Last2

    If you think about it, though, pretty much nothing ever could because there is no way to reliably distinguish between both of the following within the same data list...

    First1 First2 Last

    First Last1 Last2

    as there would be no way to tell 100% of the time whether the middle "word" belongs to the First or the Last name. Anyway, a choice had to be made and since First1 First2 Last would be processed identically to First Middle Last, that was the one that was chosen.

    Okay, enough of all that introduction stuff, here is the formula that will properly reverse (with an introduced comma separator) probably better than 99.99% of the names in any list you would ever have to process...

    =MID(A2&", "&A2,FIND(" ",A2,FIND(" ",A2)+COUNTIF(A2,"* * *"))+1,LEN(A2)+1)
    Last edited by Rick Rothstein; 10-09-2012 at 02:58 AM.
    mail2debraj likes this.

  2. #2
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    =MID(A2&", "&A2,FIND(" ",A2,FIND(" ",A2)+COUNTIF(A2,"* * *"))+1,LEN(A2)+1)
    i am curious to learn this. would it be possible Rick to breakdown this formula. what exactly this multple find and countif did?

  3. #3
    Junior Member
    Join Date
    Oct 2013
    Posts
    1
    Rep Power
    0

    Lightbulb

    Quote Originally Posted by jamilm View Post
    i am curious to learn this. would it be possible Rick to breakdown this formula. what exactly this multple find and countif did?
    Rick's method:

    The countif is counting if there are a number of spaces as far as I can tell.
    Rick's method works with max 3 words - more than 3 words, it fails to reverse proper
    Looks like a simpler version than mine, however, mine works with more than 3 names (or 2 spaces).

    My method is:

    =MID(MASTER!B5&", "&MASTER!B5,FIND("^^",SUBSTITUTE(MASTER!B5," ","^^",LEN(MASTER!B5)-LEN(SUBSTITUTE(MASTER!B5," ",""))))+1,LEN(MASTER!B5)+1)

    B5 is the single sell with format of:
    "First Last"
    "First Middle Last"
    "First Middle1 Middle2 Last"

    note: format for Rick's or my versions MUST have at least one space, ie: 2 words

    It may be complicated, but gets the job done, and the only way I could get it to work proper.


    As for explaining code, the best way to learn is to dissect it yourself, and learn how each function works. That is mainly how I learned programming, computers, driving, drinking and you can guess the rest.

  4. #4
    Junior Member
    Join Date
    Jan 2014
    Posts
    2
    Rep Power
    0

    Convert to UDF?

    Hello,

    I am enjoying Rick's code for switching first/last to last/first:

    Code:
    =MID(A2&", "&A2,FIND(" ",A2,FIND(" ",A2)+COUNTIF(A2,"* * *"))+1,LEN(A2)+1)
    I use it often in my little database for running golf tournaments. I would like to turn this line into a function in VBA. I have been trying, and trying, but I'm pretty new to VBA, have yet to write my own function successfully, and can't get the code written properly. Any help would be greatly appreciated.

    The way I would like to use the function is to place it in a blank adjacent cell, and write the value to the blank cell rather than changing the original, just as Rick's code does when placed in the spreadsheet.

    Thank You in advance,
    Kevin

  5. #5
    snb
    snb is offline
    Senior Member
    Join Date
    Jun 2012
    Posts
    246
    Rep Power
    3
    I'd prefer:

    =MID(TEXT(A2,"@ @"),SEARCH(" ",A2)+1,LEN(A2))

    In VBA

    Code:
    function F_snb(c00)
      F_snb=split(c00)(1) & " " & split(c00)(0)
    end function
    in Cell B2

    =F_snb(A2)
    Last edited by snb; 01-06-2014 at 10:32 PM.
    KevCarter likes this.

  6. #6
    Junior Member
    Join Date
    Jan 2014
    Posts
    2
    Rep Power
    0
    Quote Originally Posted by snb View Post
    I'd prefer:

    =MID(TEXT(A2,"@ @"),SEARCH(" ",A2)+1,LEN(A2))

    In VBA

    Code:
    function F_snb(c00)
      F_snb=split(c00)(1) & " " & split(c00)(0)
    end function
    in Cell B2

    =F_snb(A2)
    WOW, I never got close to anything that looks like that. I have so much to learn. Thank you very much for taking the time in helping me!

    Kevin

LinkBacks (?)

  1. 02-21-2014, 08:17 PM
  2. 10-09-2012, 03:01 AM

Similar Threads

  1. VBA Versions of my "Get Field" and "Get Reverse Field" formulas
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 0
    Last Post: 05-01-2013, 07:53 PM
  2. Replies: 5
    Last Post: 04-18-2013, 03:00 AM
  3. Click On link entitled "Web Timeclock"
    By mrmmickle1 in forum Excel Help
    Replies: 0
    Last Post: 01-24-2013, 04:53 AM
  4. Follow-up to "Excel Number Format: Indian Style Comma Separation"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 04-14-2012, 11:16 PM
  5. Ordinal Suffix (i.e., "st", "nd", "rd" and "th")
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 0
    Last Post: 03-20-2012, 04:16 AM

Tags for this Thread

Posting Permissions

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