Text & String Formulas
Extract Middle Name(s) from Full Name (MID, FIND & SUBSTITUTE)
Isolate one or multiple middle names between the first and last space of a full name—and cleanly return "" when a person has only a first and last name—using TRIM, SUBSTITUTE nth-instance markers, and MID.
Handling 2-word, 3-word, and 4-word names without #VALUE! crashes!
Splitting full names into First, Middle, and Last columns trips up naive FIND(" ", A2, FIND(" ", A2)+1) formulas in two classic ways: (1) two-word names like "Ivan Petrov" have only ONE space, throwing #VALUE! or leaking the surname into Middle Name, and (2) four-word names like "Jean Luc De Vries" have TWO middle tokens before the final surname!
Counting spaces after TRIM(A2) via N_spaces = LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) solves both cases cleanly! When N_spaces < 2, the person has no middle name so return ""; when N_spaces >= 2, mark the LAST space uniquely with "~" via SUBSTITUTE(TRIM(A2), " ", "~", N_spaces) and slice everything strictly between the first space and "~"!
| Trimmed Full Name Input | Space Count N_spaces | Middle Name Extracted | Edge-Case Behavior |
|---|---|---|---|
| "Elena Marie Kostova" | 2 spaces | "Marie" | Single Middle Name |
| "Arthur Conan Ignatius Doyle" | 3 spaces | "Conan Ignatius" | Preserves Multi-Word Middle! |
| "Ivan Petrov" (No Middle Name) | 1 space (< 2) | "" (Empty String) | Zero Surname Leakage! |
Extract Single or Multi-Word Middle Name Safely
Returns "" when fewer than 2 spaces exist after TRIM; otherwise slices between the first space and the nth (last) space.
=IF(LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) < 2, "", MID(TRIM(A2), FIND(" ", TRIM(A2)) + 1, FIND("~", SUBSTITUTE(TRIM(A2), " ", "~", LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")))) - FIND(" ", TRIM(A2)) - 1))Formatted Middle Initial with Period (e.g., "M.")
Extracts the uppercase first letter of the middle name followed by "." (or "" if no middle name exists).
=IF(LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) < 2, "", UPPER(MID(TRIM(A2), FIND(" ", TRIM(A2)) + 1, 1)) & ".")Try the example
Use the original values or change them to test your understanding.