Get the First Name and Last Name From a Full Name in Excel

by

To split Anil Kumar into Anil and Kumar, use TEXTBEFORE and TEXTAFTER in Microsoft 365, or LEFT and FIND in any version. These formulas also handle middle names and people with only one name.

First name (365)=TEXTBEFORE(A2," ",,,1)
Last name (365)=TEXTAFTER(A2," ",-1,,,"")
Any versionLEFT + FIND

Excel 365: TEXTBEFORE and TEXTAFTER

=TEXTBEFORE(A2," ",,,1)
TEXTBEFORE returning first names and TEXTAFTER returning last names
  • TEXTBEFORE returns the text before the first space.
  • The 1 in the 5th argument treats the end of the text as a space, so a single name like Meera returns Meera instead of #N/A.

For the last name, search for the last space with -1:

=TEXTAFTER(A2," ",-1,,,"")

Sachin Ramesh Tendulkar returns Tendulkar. The final "" returns a blank for a single name. See TEXTAFTER.

Any version: LEFT and FIND

=LEFT(A2,FIND(" ",A2&" ")-1)
LEFT and SEARCH extracting the first name
  1. FIND(" ",A2&" ") finds the position of the first space. Adding a space to the end (A2&" ") means a single name still has one.
  2. -1 steps back from the space.
  3. LEFT returns that many characters from the start.

SEARCH works the same as FIND here; the difference is that SEARCH ignores case and allows wildcards. For the last name in older versions:

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

It replaces each space with 100 spaces, takes the last 100 characters and trims them, which leaves the last word. For a single name it returns the whole name.

Without a formula: Flash Fill

Type the first name for the first two people in the next column, then press Ctrl + E. Excel fills in the rest by following your pattern. It doesn't update if the names change.

Tips

  • Extra spaces break the formulas. Wrap the name in TRIM first: =TEXTBEFORE(TRIM(A2)," ",,,1).
  • Titles such as Dr. or Mr. become the first name. Remove them first with SUBSTITUTE.
  • To split a name into all its parts at once in Excel 365, use =TEXTSPLIT(A2," ").

Related