+ Reply to Thread
Results 1 to 6 of 6

Split Names (Some with Middle Initial)

  1. #1
    Forum Contributor
    Join Date
    11-14-2004
    Location
    Watford, England
    MS-Off Ver
    Microsoft Office 365
    Posts
    129

    Split Names (Some with Middle Initial)

    Hi All,

    I have a long list of names in one column, some just first and last, some with middle initial and some with middle name.

    What I need to do is just end up with a list in 2 cols, Firstname and Lastname

    Any help is appreciated.

    Thanks
    Last edited by fastcar; 11-16-2018 at 10:20 AM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Split Names (Some with Middle Initial)

    so you have like John Q Public in cell A2 and want John in B2 and Public in C2.
    try these
    =LEFT(A2,FIND(" ",A2)-1) in B2 and
    =RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))) in C2
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Split Names (Some with Middle Initial)

    Enter formula in cell B2 and drag formula across to cell D2 and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D
    1 Names First Name Middle Name Last Name
    2 Justin Wolf Justin Wolf
    3 Allan J Green Allan J Green
    4 Jerry Grant Jerry Grant
    5 Steven B Paul Steven B Paul
    6 Henry Ward Beecher Henry Ward Beecher
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Split Names (Some with Middle Initial)

    UDF (user defined function) will do?
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Split Names (Some with Middle Initial)

    you marked it as solved, so what did you use?

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Split Names (Some with Middle Initial)

    In C2 then copied down

    =LEFT(B2,FIND(" ",B2)-1)

    In D2 then copied down

    =RIGHT(B2,LEN(B2)-AGGREGATE(14,6,ROW(INDIRECT("1:"&LEN(B2)))/(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)=" "),1))
    Attached Files Attached Files

+ 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. Replies: 1
    Last Post: 09-14-2016, 04:55 AM
  2. Replies: 3
    Last Post: 03-05-2015, 02:33 PM
  3. [SOLVED] Split a name string into first name , middle initial and last name
    By Lady_Shaz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-24-2013, 01:39 AM
  4. Replies: 2
    Last Post: 06-28-2012, 01:35 PM
  5. Replies: 3
    Last Post: 03-09-2012, 03:19 PM
  6. Split only the middle names
    By niladri20005 in forum Excel General
    Replies: 1
    Last Post: 06-13-2011, 07:43 AM
  7. Remove middle initial from "first name middle initial"
    By Justin F. in forum Excel General
    Replies: 15
    Last Post: 09-26-2005, 02:05 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