Split a full name into first and last name

How it works, step by step

1. 01A2 holds the full name, Priya Natarajan.
2. 02TEXTBEFORE(A2," ") returns everything before the first space: Priya.
3. 03TEXTAFTER(A2," ",-1) returns everything after the last space. The -1 counts spaces from the end, so a middle name is skipped and Mary Ann Lopez gives Lopez.
4. 04HSTACK places the two results side by side, so one formula in B2 fills both B2 and C2.
5. 05In Google Sheets, SPLIT(A2," ") breaks the name at every space and spills each part into its own column.

The same logic in four tools

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

=SPLIT(A2," ")

SELECT full_name,
       split_part(full_name, ' ', 1) AS first_name,
       regexp_replace(full_name, '^.* ', '') AS last_name
FROM contacts;

import pandas as pd

names = contacts["full_name"].str.strip()
contacts["first_name"] = names.str.split(" ", n=1).str[0]
contacts["last_name"] = names.str.rsplit(" ", n=1).str[-1]

Mistakes that break it

• !TEXTBEFORE, TEXTAFTER, and HSTACK need Microsoft 365 or Excel 2024. In older Excel, use =LEFT(A2,FIND(" ",A2)-1) for the first name and =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)) for the last name.
• !A single-word name like Cher has no space, so TEXTBEFORE returns #N/A. Its sixth argument sets a fallback: =TEXTBEFORE(A2," ",,,,A2) returns the whole name instead.
• !Double spaces or a trailing space split the name in the wrong place. Wrap A2 in TRIM first.
• !Google Sheets SPLIT gives a middle name its own column, so Mary Ann Lopez fills three cells and the last name lands in D. Suffixes like Jr. cause the same problem in every version.

Common questions

How do I split first and last name in Excel?

In Microsoft 365, use =TEXTBEFORE(A2," ") for the first name and =TEXTAFTER(A2," ",-1) for the last name. In older versions, use =LEFT(A2,FIND(" ",A2)-1) for the first name.

How do I split a name into two columns in Google Sheets?

Use =SPLIT(A2," "), which spills each word into its own column. For a one-time split without formulas, use Data > Split text to columns.

How do I get the last word in a cell in Excel?

Use =TEXTAFTER(A2," ",-1). The -1 counts from the end of the text, so it returns whatever follows the last space.