Hi,

I came up with the following approach, which will work even if there is more
than one middle initial, middle name, or any combination thereof.

If the name is in A2, enter the following formulas in B2, C2, and D2.

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

In C2,
=IF(LEN(TRIM(A2))=LEN(B2)+LEN(D2)+1,"
",MID(TRIM(A2),LEN(B2)+2,LEN(TRIM(A2))-LEN(B2)-LEN(D2)-2))

In D2,
=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND("#",SUBSTITUTE(TRIM(A2),"
","#",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ","")))))


Note: The formula in C2 will work only after the formula in D2 is also
entered.

One issue with my formulas:
If the last name ends with a suffix such as "Sr", "Jr" or a roman numeral
such as "II" or "III" (with a space between the name and the suffix), the
formulas would misinterpret the middle and last names and wrongly return only
the suffix as the last name (i.e., anything after the last space in the
entire name).

Secondly, there should be a space between an intital and a name, a name and
an initial, or two initials. Thus "William J. Burrows" will work but not
"William J.Burrows"

Please click "Yes" if this is helpful.

Best regards,
B. R. Ramachandran

"dfw" wrote:

My imported "Name" column (column A) contains different name formats: First
Name Last Name; First Name Middle Initial Last Name; and, in some cases,
First Name Middle Name Last Name (for example: Mary Smith, John B. Doe,
William James Burrows).
Is there a simple way to convert all names to three columns with the Given
Name in Column B, the Middle Initial or Name in Column C, and the last name
in Column D?