Split Full Name into First and Last Name in Excel and Google Sheets
Purpose
Splitting a full name column into separate first and last name columns is one of those data-cleaning tasks that comes…
Splitting a full name column into separate first and last name columns is one of those data-cleaning tasks that comes up constantly when importing contacts, employee records, or customer lists from external systems. The data arrives as one combined field and you need it in two. In Excel 365 and Google Sheets, TEXTSPLIT does this in a single formula. In any version of Excel, LEFT and FIND extract the first name and MID with FIND extracts the last name. The logic is the same in both cases: find the space character that separates first and last name, then grab everything to the left of it for the first name and everything to the right of it for the last name. I use this combination so often it is basically muscle memory now. Once you understand how FIND locates the space, you can adapt the same pattern to split on any delimiter — a comma, a hyphen, an underscore — making it one of the most reusable text formulas in the toolkit.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=LEFT(A2,FIND(" ",A2)-1) / =MID(A2,FIND(" ",A2)+1,LEN(A2))
Two separate formulas — one for the first name, one for the last name.
Arguments
| Argument | Required | Description |
|---|---|---|
| FIND arguments: find_text | Required | The character to locate — use " " (a quoted space) to find the space between first and last name. |
| within_text | Required | The full name cell, e.g. A2. |
| LEFT arguments: text | Required | The full name cell. |
| num_chars | Required | FIND(" ",A2)-1 — one less than the space position, giving only the first name. |
| MID arguments: text | Required | The full name cell. |
| start_num | Required | FIND(" ",A2)+1 — one character after the space, where the last name starts. |
| num_chars | Required | LEN(A2) — a safe overcount that captures all remaining characters. |
How it works
FIND locates the position of the space character inside the full name string — for Alice Johnson that position is 6. LEFT takes the characters from the start up to but not including that position: LEFT(A2,6-1) returns the first 5 characters, which is Alice. MID starts one character after the space: MID(A2,6+1,LEN(A2)) starts at position 7 and takes up to LEN(A2) characters, which always captures the rest of the string regardless of length — returning Johnson. LEN(A2) as the length argument is a safe overcount because MID simply stops at the end of the string when there are no more characters. This two-formula pattern works perfectly for simple First Last names. For names with middle names, the first-name formula is unchanged but the last-name formula needs to find the last space rather than the first, which uses a SUBSTITUTE trick to replace only the final space with a unique marker. In Excel 365 and modern Google Sheets, the TEXTSPLIT function handles all of this in one formula with a space as the delimiter.
Examples
1
Extract the first name and last name from a full name column into two separate columns.
| A | B | C | |
|---|---|---|---|
| 1 | Full Name | First Name | Last Name |
| 2 | Alice Johnson | =LEFT(A2,FIND(" ",A2)-1) | =MID(A2,FIND(" ",A2)+1,LEN(A2)) |
| 3 | Bob Williams | Bob | Williams |
| 4 | Carol Davis | Carol | Davis |
| 5 | Dan Lee | Dan | Lee |
| 6 | Eve Parker | Eve | Parker |
| 7 | Finn Murphy | Finn | Murphy |
Row 2: Alice
— FIND locates the space in Alice Johnson at position 6. LEFT(A2,6-1) returns the first 5 characters: Alice.
The Last Name formula =MID(A2,FIND(" ",A2)+1,LEN(A2)) for Alice Johnson starts at position 7 and takes up to LEN(A2) characters, returning Johnson. Both formulas go in separate columns and copy down the full list.
2
Extract the last name from names that include a middle name, using the last space as the separator.
| A | B | |
|---|---|---|
| 1 | Full Name | Last Name |
| 2 | Alice Marie Johnson | =MID(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,LEN(A2)) |
| 3 | Bob James Williams | Williams |
| 4 | Carol Ann Davis | Davis |
| 5 | Dan Lee | Lee |
| 6 | Eve Grace Parker | Parker |
Row 2: Johnson
— Alice Marie Johnson has two spaces. The formula replaces only the last space with * using SUBSTITUTE with an instance count, then FIND locates the * and MID extracts everything after it — returning Johnson.
The inner SUBSTITUTE(A2," ","") removes all spaces to count how many spaces exist. That count is used to replace only the last space with * which acts as the position marker. This pattern works for any number of words — two, three, or more.
3
Use TEXTSPLIT in Excel 365 or Google Sheets to split a full name into first and last name in a single formula.
| A | B | C | |
|---|---|---|---|
| 1 | Full Name | First | Last |
| 2 | Alice Johnson | =TEXTSPLIT(A2," ") | [spills to next cell] |
| 3 | Bob Williams | Bob | Williams |
| 4 | Carol Davis | Carol | Davis |
| 5 | Dan Lee | Dan | Lee |
| 6 | Eve Parker | Eve | Parker |
Row 2: Alice
— TEXTSPLIT(A2," ") splits Alice Johnson at the space and spills Alice into the formula cell and Johnson into the adjacent cell automatically.
Google Sheets uses SPLIT instead of TEXTSPLIT: =SPLIT(A2," "). Both work identically. TEXTSPLIT and SPLIT are the fastest approach for simple First Last names and handle middle names by spilling additional columns for each word.
Common use cases
1. Separate a contact import where first and last names arrived in one column into two individual fields
=LEFT(A2,FIND(" ",A2)-1)
2. Extract last names only from an employee roster for alphabetical sorting
=MID(A2,FIND(" ",A2)+1,LEN(A2))
3. Build a personalised email greeting using just the first name from a full name column
="Dear "&LEFT(A2,FIND(" ",A2)-1)&","
4. Separate names with middle names by extracting the last word using the last-space pattern
=MID(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,LEN(A2))
5. Split names in Excel 365 or Google Sheets in one step using TEXTSPLIT or SPLIT
=TEXTSPLIT(A2," ")
Common errors
#VALUE! error
The full name cell contains no space — it is a single word or is blank. FIND cannot locate a space and returns an error which propagates through LEFT and MID.
Fix: Wrap in IFERROR: =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2) which returns the whole cell content when no space is found, handling single-word names gracefully.
First name includes extra characters
There are multiple spaces at the start of the name or between first and last name — FIND returns the position of the first space which may be a leading space.
Fix: Use TRIM(A2) around the cell reference to remove leading, trailing, and extra internal spaces before applying FIND.
Last name is cut short or includes middle name
The formula targets the first space for the last name but the name has a middle name, so MID returns middle and last together.
Fix: Use the last-space version of the formula with SUBSTITUTE to find the final space, or use TEXTSPLIT which spills each word into its own column.
Tips and variations
Wrap both formulas in TRIM to clean extra spaces before splitting
If source data has inconsistent spacing, TRIM(A2) inside the formula ensures FIND locates the correct space rather than a stray leading space. This one habit prevents the most common split-name errors I encounter in client data.
=LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)
Use TEXTSPLIT or SPLIT for multi-word names in modern Excel and Google Sheets
TEXTSPLIT(A2," ") spills every word into its own cell — first, middle, and last — without multiple formulas. For Google Sheets use SPLIT(A2," "). Both are the fastest approach for one-time or live data splitting.
=TEXTSPLIT(A2," ")
Build a Last, First format for sorted lists using concatenation
Combine the last-name and first-name formulas with & to create a sort-friendly Last, First format from any full name column. This is the format most HR and CRM systems expect for alphabetical employee lists.
=MID(A2,FIND(" ",A2)+1,LEN(A2))&", "&LEFT(A2,FIND(" ",A2)-1)
Frequently asked questions
For a two-word full name like Alice Johnson, use LEFT(A2,FIND(" ",A2)-1) to extract the first name and MID(A2,FIND(" ",A2)+1,LEN(A2)) to extract everything after the space as the last name. If names include middle names or titles, adjust by searching from the right for the last name or use TEXTSPLIT in Excel 365.
Use MID(A2,FIND(" ",A2)+1,LEN(A2)) for a simple First Last format — it returns everything after the first space. For names with middle names, use a right-search approach: RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))) extracts the last word regardless of how many words precede it.
Excel 365 has TEXTSPLIT which splits text by a delimiter into an array: =TEXTSPLIT(A2," ") splits a full name into separate cells by the space character. Flash Fill (Ctrl+E) can also split names automatically by learning from an example you type in the adjacent column. Both are faster than manual formulas for one-time data cleaning.
For middle names, extract the first word with LEFT(A2,FIND(" ",A2)-1) and the last word with a nested SUBSTITUTE-FIND approach that finds the position of the last space. For suffixes like Jr. or Sr., the last word will be the suffix — you may need to strip it separately or use a different delimiter like a comma if the data allows.
Text to Columns (Data tab, Text to Columns, Delimited, Space) splits all names in one step and pastes static values into new columns. Formulas split names dynamically so if the source data changes the split columns update automatically. Use Text to Columns for a one-time cleanup. Use formulas when the source data is live and updated regularly.
Related Formulas
Related Functions