Answers / Question 8
Split full names into first and last when some have a middle name (Excel 365)
TEXTBEFORE and TEXTAFTER formulas that split on the last space, handle one-word names, and keep suffixes like Jr. with the surname.
The problem
A column of full names needs to become separate first-name and last-name columns, but the names aren't uniform: some include a middle name or initial, and splitting on the first space puts the middle name in the wrong place.
Inspired by a public question on MrExcel Message Board. The problem above is described in our own words and the solution below is ours.
The fix (Excel 365 / 2024)
Split on the last space instead of the first. The -1 instance number means “count from the end”.
=TEXTBEFORE(TRIM(A2), " ", -1, , , TRIM(A2))=TEXTAFTER(TRIM(A2), " ", -1, , , "")The last argument is what to return when there's no space, so a single-word name lands in the first column and leaves the last-name column empty.
Keeping Jr., Sr., II and III with the surname
=LET(n, TRIM(A2), w, TEXTAFTER(n, " ", -1, , , ""), IF(OR(w={"Jr.","Sr.","II","III","IV"}), TEXTBEFORE(n, " ", -2, , , n), TEXTBEFORE(n, " ", -1, , , n)))=LET(n, TRIM(A2), w, TEXTAFTER(n, " ", -1, , , ""), IF(OR(w={"Jr.","Sr.","II","III","IV"}), TEXTAFTER(n, " ", -2, , , n), w))Older Excel versions
=LEFT(TRIM(A2), FIND(" ", TRIM(A2)&" ")-1)=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), 100))Good to know
- No formula can tell that Van Buren or de la Cruz is a two-word surname. Filter for names with three or more words and review those by hand.
- Add any other suffixes you see (MD, PhD, Jr without the dot) to the list inside the braces.
Formulas follow documented Google Sheets / Excel behavior; they aren't machine-tested, so try them on a copy first. Always try new automations on a copy of your sheet first.