What You Will Learn
Your contact list stores full names like "Tony Stark" in column A. You need the first name in column B and the last name in column C. The space between them is the split point — everything before it is the first name, everything after it is the last name.
This pattern comes up in CRM imports, email campaigns, event rosters, and anytime a list stores names as a single block. The two formulas below handle both sides of the split.
First name — find the space, return everything to its left:
=LEFT(A2, SEARCH(" ", A2) - 1)
Last name — measure the total length, subtract the space position, return what is left on the right:
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))
Fill both formulas down from row 2 to row 6. The audit section at the bottom confirms how many rows have complete split results and how many total records exist.
How the Formulas Work
Both formulas start by finding the space. SEARCH(" ", A2) returns the position of the first space inside the name — for "Tony Stark" that is 5.
LEFT takes that position and subtracts 1 so the space itself is excluded. It returns everything from the start up to character 4 — "Tony".
RIGHT needs to know how many characters to take from the end. LEN(A2) gives the total (10), and subtracting the space position (5) leaves 5 — enough to return "Stark".
This is the standard first-space-split pattern. SEARCH locates the delimiter, LEFT takes the left side, RIGHT with LEN takes the right side. The same pattern works for any two-part value separated by a known character — names, codes, or labels.
The space is the split point. LEFT takes everything before it, RIGHT takes everything after it.
The Audit Formulas
- B9:
=COUNTIFS(B2:B6,"<>",C2:C6,"<>") — counts rows where both name columns are filled.
- B10:
=COUNTA(A2:A6) — counts total records in the list.
COUNTIFS with the "<>" condition checks that a cell is not empty. By requiring both B and C to be filled, it confirms the split worked on that row. COUNTA gives the total roster size — the difference between the two numbers tells you how many rows still have incomplete splits.
Alternative: TEXTSPLIT
In newer Excel versions, =TEXTSPLIT(A2, " ") splits the name across two columns in one step. It is faster to write but only works in Excel 365 and later. The LEFT/RIGHT approach works in every version.
TEXTSPLIT splits in one step but requires a modern Excel version.
This pattern assumes each name has exactly one space separating first and last. If the data includes middle names, extra spaces, or missing names, the formulas need an extra cleanup step before splitting.
Write a LEFT+SEARCH formula in B2:B6 to extract first names and a RIGHT+LEN+SEARCH formula in C2:C6 to extract last names. Then use COUNTIFS in B9 for successful splits and COUNTA in B10 for total records.