Share via

Change name from: last name, first name middle initial to first name last name

Anonymous
2014-06-17T18:22:43+00:00

I am trying to reformat a string of names to just be "First Name Last Name" ie. John Smith.

Currently I have names with and without a middle initial in them that are formatted like this:

Last, First MI

Last, First

Smith, John E

Smith, John

Can someone help me with the formulas that would do this? Thank you

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2014-06-17T20:53:50+00:00

    Hi,

    =TRIM(MID(LEFT(A1&" ",SEARCH(" ? ",A1&" A ")),FIND(",",A1)+1,99)&LEFT(A1,FIND(",",A1)-1))

    Regards

    20+ people found this answer helpful.
    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-06-17T18:44:08+00:00

    Hi

    So for this

    Smith John J

    you want this

    John Smith

    =SUBSTITUTE(MID(LEFT(A1,FIND(" ",A1&" ",FIND(" ",A1&" ")+1))&" "&A1,FIND(" ",A1)+1,LEN(A1)),",","")

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-06-17T18:43:44+00:00

    Try this formula (change A1 to suit your needs):

    =MID(A1,FIND(" ",A1,1)+1,IFERROR(FIND(" ",A1,FIND(" ",A1,1)+1),LEN(A1)+1)-FIND(" ",A1,1)-1)&" "&LEFT(A1,FIND(",",A1,1)-1)

    Hope this helps,

    Eric

    1 person found this answer helpful.
    0 comments
  3. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2014-06-17T18:36:46+00:00

    If your names starts in A1, put following formula in a cell and drag down

    =IF(LEN(TRIM(RIGHT(A1,2)))=1,MID(A1,1,FIND("*",SUBSTITUTE(A1," ","*",2))-1),A1)

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2014-06-17T18:56:35+00:00

    This works great...now I just found another issue. I have a few people that have 2 first names and/or 2 last names that are seperated by a space.

    Example:

    Smith, Ana Karen J

    Last, First First MI

    Van Deets, Greg M

    Last Last, First MI

    Can you help to reformat to be:

    Ana Karen Smith

    First First Last

    Greg Van Deets

    First Last Last

    Thank you

    0 comments