How to Extract First and Last Name in Excel & Google Sheets

Cleaning contact lists, CRM exports, and payroll rosters frequently requires separating a single Full Name column into distinct First Name and Last Name columns.

---

Method 1: Modern Formula (Excel 365 & Google Sheets)

Using TEXTSPLIT (Spills Both Names at Once):

=TEXTSPLIT(A2, " ")

Using TEXTBEFORE & TEXTAFTER:

• First Name:

  =TEXTBEFORE(A2, " ")

• Last Name (takes the last word, even with middle names):

  =TEXTAFTER(A2, " ", -1)

---

Method 2: Universal Classic Formulas (All Excel Versions)

If your spreadsheet must work in older versions of Excel:

• First Name Formula:

  =LEFT(A2, SEARCH(" ", A2) - 1)

• Last Name Formula:

  =RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

---

Cleaned Data Table Example

Full Name (A)	First Name Formula	First Name (B)	Last Name Formula	Last Name (C)
Alexander Hamilton	=LEFT(A2, SEARCH(" ", A2)-1)	Alexander	=RIGHT(A2, LEN(A2)-SEARCH(" ", A2))	Hamilton
Sarah Connor	=LEFT(A3, SEARCH(" ", A3)-1)	Sarah	=RIGHT(A3, LEN(A3)-SEARCH(" ", A3))	Connor
Marcus Aurelius	=LEFT(A4, SEARCH(" ", A4)-1)	Marcus	=RIGHT(A4, LEN(A4)-SEARCH(" ", A4))	Aurelius