Search code examples
stringexcelexcel-formulaworksheet-function

Extract two names from a string

In Excel, assume 

A2 = Last_Name1 First_Name1 MI1;#8;#Last_Name2 First_Name2 MI2;#102

In B2, I want to see the last name, first name and middle initial of the first person listed. So I use this:

=MID(A2,1,FIND(";",A2,1)-1)

Result: Last_Name1 First_Name1 MI1

In C2, what do I need to write to see the last name, first name and middle initial of the second person listed.

Result: Last_Name2 First_Name2 MI2

My thought is to start from the right and get everything between the second "#" and ";" (#Last_Name2 First_Name2 MI2;).

Any help is appreciated.

Solution

  • In cell C2, enter this formula:

    =LEFT(MID(A2,2+FIND("|",SUBSTITUTE(A2,";","|",2)),99),LEN(MID(A2,2+FIND("|",SUBSTITUTE(A2,";","|",2)),99))-5)

    Because of @pnuts notion, I think this version is even better

    =LEFT(MID(A2,1+FIND("|",SUBSTITUTE(A2,"#","|",2)),99),FIND(";",MID(A2,1+FIND("|",SUBSTITUTE(A2,"#","|",2)),99))-1)