EXCEL Parse Last, FirstName MiddleName

EXCEL Parse Last, FirstName MiddleName – There are a few formulas out there used to parse names from one cell to individual columns. This formula works great for extracting the last, first and middle names from a column that has them all together with the last name separated a comma.

 This assumes your data starts in Column A row 1.

=IF(IFERROR(FIND(” “, A1, SEARCH(” “,A1)+1),), TRIM(MID(A1,SEARCH(” “,A1), SEARCH(” “,A1,SEARCH(” “, A1) + 1) – SEARCH(” “,A1) ) ), TRIM(REPLACE(A1, 1, SEARCH(“,”, A1, 1), “”)))
The above formula extracts the first name “Aimy” from “Smith, Aimy A”. It works 99 percent of the time as long as the names are separated a comma and a space e.g. “, “. However, there are some complicated names that it does not work on such as: “Doe Jr., Rudy E.”

=IF(IFERROR(FIND(” “, A1, SEARCH(“, “, A1)+1), ), TRIM(MID(A1, SEARCH(“, “, A1) + 1, SEARCH(” “, A1, SEARCH(” “, A1) + 1) – SEARCH(” “, A1))), TRIM(REPLACE(A1, 1, SEARCH(“, “, A1, 1), “”) ) )

The above formula extracts “John” from “Van Brown, John B”. It works to get the first name from names with spaces in the last name.

=IF(IFERROR(FIND(” “, A1, SEARCH(” “,A1) + 1),), RIGHT(A1, LEN(A1) – SEARCH(” “, A1, SEARCH(” “,A1) + 1)), “”)

The above formula extracts the middle name “A” from “Smith, Aimy A”.

=TRIM(LEFT(A1,FIND(“,”,A1)-1))

The last one above extracts the last name, “Doe”, from “Doe, John G”