Text
Split first, middle, and last names (plus "Last, First" comma format)
Extract given names, optional middle names, and surnames cleanly with TEXTBEFORE and TEXTAFTER—and flip "Last, First" payroll strings.
Anchor from both ends with instance_num = 1 and -1
In "Grace Brewster Hopper", TEXTBEFORE(TRIM(A2), " ") grabs everything before the FIRST space ("Grace"), while TEXTAFTER(TRIM(A2), " ", -1) grabs everything after the LAST space ("Hopper")—working equally well on 2-word names like "Ada Lovelace".
Any words strictly between the first and last space are middle names, present whenever space count >= 2.
This assumes a space-delimited first/middle/last convention. It does not reliably identify compound surnames, suffixes or every naming culture. Single-token and empty inputs need a separate policy; prefer explicit name fields when accuracy matters.
| FullName (A) | First Name | Middle Name | Last Name |
|---|---|---|---|
| Grace Brewster Hopper | Grace | Brewster | Hopper |
| Ada Lovelace | Ada | (empty) | Lovelace |
First, middle, last, and "Last, First" reversal formulas
For payroll exports formatted as "Hopper, Grace", TRIM(TEXTAFTER(B2, ",")) & " " & TRIM(TEXTBEFORE(B2, ",")) flips the name to "Grace Hopper".
=TEXTBEFORE(TRIM(A2), " ")Optional middle-name extractor
When fewer than 2 spaces exist (such as "Ada Lovelace"), the formula returns "" instead of repeating a name.
=LET(t, TRIM(A2), IF(LEN(t) - LEN(SUBSTITUTE(t, " ", "")) < 2, "", TEXTBEFORE(TEXTAFTER(t, " "), " ", -1)))Try the example
Use the original values or change them to test your understanding.