extract last name only

  • I'm looking to pull into Column B the last name only from the "full name" column (column C).
    Right now, in Column B I have the formula =UPPER(MID(C3,FIND(" ",C3)+1,50)) to pull in the last name from the full name that is in column C.
    The problem is that when column C has a middle initial or a nickname, column B ends up having the middle initial and the last name.
    For example, if Column C has John "Buster" Jones, I get "Buster" Jones in column B but I only want Jones; or if Column C has Mary T. Smith, I get T. Smith in Column B but I only want SMITH
    Is there a better approach so I only get the last name?

  • Re: extract last name only

    This should get just the last name regardless of middle initial or middle name.

    =TRIM(IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),MID(A1,FIND(" ",A1),9999),MID(A1,FIND(" ",A1,FIND(" ",A1)+1),9999)))

  • Re: extract last name only

    A formula coming from this forum once in the past.
    A1 = your data
    B1 = TRIM(REPLACE(A1,1,FIND("^^",SUBSTITUTE(TRIM(A1)," ","^^",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))),""))

    Triumph without peril brings no glory: Just try

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!