Results 1 to 6 of 6

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    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:28 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: 4
    Last Post: 06-02-2017, 06:15 PM
  2. Replies: 5
    Last Post: 04-18-2013, 02:30 AM
  3. Click On link entitled "Web Timeclock"
    By mrmmickle1 in forum Excel Help
    Replies: 0
    Last Post: 01-24-2013, 04:23 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, 10:46 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, 03:46 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
  •