Column A has full names like Jane Q Public. Extract first and last name

公式

=TEXTBEFORE(A2, " ")

说明

TEXTBEFORE returns everything left of the first space (first name). Use TEXTAFTER with a negative instance to take the last word as the surname.

步骤

  1. Assume names are in A2 downward.
  2. TEXTBEFORE(…, " ") is the first name.
  3. TEXTAFTER(…, " ", -1) is the last name.
  4. On Excel 2016 use LEFT/RIGHT/FIND instead.

变体

Last name (365)

Negative instance counts from the end, so middle names stay in the middle.

=TEXTAFTER(A2, " ", -1)

Excel 2016 first name

Appending a space avoids #VALUE! on single-word names.

=LEFT(A2, FIND(" ", A2&" ")-1)

Excel 2016 last name

Pads spaces, then takes the last token. Works without TEXTSPLIT.

=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 99)), 99))