Separate out first and last name
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Separate out first and last name
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2015
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Separate out first and last name

    Hey all!

    I have a very long list of employees in which I need to extract their names into a consistent format.

    I'm using the below formulas with a 99% success rate, however employees with a suffix of "JR" or "II" (etc) are throwing a monkey wrench in the calculation.

    First:
    =MID(P14082,FIND(" ",P14082,1)+1,256)

    Last:
    =LEFT(P14082,(FIND(" ",P14082,1)-1))


    Here is the format of my data:

    Firstname Suffix LastName MiddleInitial


    I ONLY need the first and last name. Is there a formula out there that can overcome the 2% of my names that have a suffix?

    Thanks for the help team!

    Best,
    Chris

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Separate out first and last name

    Column1 Column1 Text After Delimiter
    Firstname Suffix LastName MiddleInitial Firstname LastName


    if this is representative source data use PowerQuery (Get&Transform)

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        TAD = Table.AddColumn(Source, "Text After Delimiter", each Text.AfterDelimiter([Column1], " "), type text),
        TBD = Table.TransformColumns(TAD, {{"Column1", each Text.BeforeDelimiter(_, " "), type text}}),
        TBTD = Table.TransformColumns(TBD, {{"Text After Delimiter", each Text.BetweenDelimiters(_, " ", " "), type text}})
    in
        TBTD
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    Dec 2015
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate out first and last name

    I've never worked with power queries before - I've tried doing some research on them, but what I've found seems to be for older versions of excel. (I have the newest version of Excel; Office 365 ProPlus 64bit) I tried opening up power pivot (which is the same as power query, I assume) and I'm not able to replicate the steps below.

    COuld you provide me with additional guidance, or point me to a newbie friendly resource on this? Or is there another way to get the data I'm looking for?

    Thank you!!

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Separate out first and last name

    so you've PowerQuery built-in (Get&Transform)
    and NO, PowerPivot is NOT the same as PowerQuery
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Separate out first and last name

    select whole column with your source data
    find in Data tab something like From Table and use it
    then in new window find Advanced Editor and replace code there with code from the post
    remeber that the name of the table must be the same in the Name Manager and in the code
    (in post above this is : Table1)

    or post small part of source data

    Last edited by sandy666; Jul 15th, 2019 at 03:19 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #6
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate out first and last name

    Nordicrx8:

    A few things to try:

    Chip Person: http://www.cpearson.com/Excel/FirstLast.htm

    I wrote up some stuff:

    https://www.mrexcel.com/forum/excel-...ighlight=kevin

    Look at post #5


  7. #7
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate out first and last name

    Since you have the newest version of Excel, have you tried Flash Fill?

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate out first and last name

    I believe you got your formulas mixed up. Anyways, try these and let me know if they work:

    First name: =LEFT(A1,FIND(" ",A1)-1)
    Last name: =LEFT(MID(MID(A1,FIND(" ",A1)+1,256),FIND(" ",MID(A1,FIND(" ",A1)+1,256))+1,256),FIND(" ",MID(MID(A1,FIND(" ",A1)+1,256),FIND(" ",MID(A1,FIND(" ",A1)+1,256))+1,256))-1)

  9. #9
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate out first and last name

    I did the following with some helper columns:


    ABCDE
    1John Kennedy FitzgeraldKennedy FitzgeraldKennedy FitzgeraldJohnKennedy
    2Gerald Jr. Ford RudolphJr. Ford RudolphFord RudolphGeraldFord
    3James Jr. Carter EarlJr. Carter EarlCarter EarlJamesCarter
    4George Bush HerbertBush HerbertBush HerbertGeorgeBush
    5John Jr. Cooledge CalvinJr. Cooledge CalvinCooledge CalvinJohnCooledge
    6William III Smith F.III Smith F.Smith F.WilliamSmith

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=RIGHT(A1,LEN(A1)-FIND(" ",A1))
    C1=IF(OR(LEFT(B1,4)="Jr. ",LEFT(B1,4)="Sr .",LEFT(B1,4)="II ",LEFT(B1,4)="III ",LEFT(B1,4)="Esq."),RIGHT(B1,LEN(B1)-4),B1)
    D1=LEFT(A1,FIND(" ",A1)-1)
    E1=LEFT(C1,FIND(" ",C1)-1)


  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate out first and last name

    I didn't account for the suffix and middle name initial in my last name formula, revised version below.

    =LEFT(MID(MID(A1,FIND(" ",A1)+1,256),IFERROR(FIND(" ",MID(A1,FIND(" ",A1)+1,256)),0)+1,256),IFERROR(FIND(" ",MID(MID(A1,FIND(" ",A1)+1,256),FIND(" ",MID(A1,FIND(" ",A1)+1,256))+1,256)),255)-1)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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