The Problem
A full name in one cell is easy to store, but not always easy to use. Many contact workflows need first names and last names in separate columns so the list can be sorted, filtered, or used in personalized messages.
This challenge splits each full name into two parts and then checks how many rows were split successfully. The structure is simple, but it teaches a very common text-cleaning pattern.
How We Solve It
The formulas work by finding the first space in the full name. That space marks the boundary between the first name and the last name, so one formula takes everything before it and another formula takes everything after it.
Method 1: LEFT with SEARCH
Method 1: Extract the first name by cutting everything before the first space.
This is the pattern used for the first-name column in the solution. SEARCH finds the space position, and LEFT returns the characters before it.
This solves the first half of the challenge because the first name sits before the space in each full name. Once the space position is known, the worksheet can return only the left side into column B.
=LEFT(A2, SEARCH(" ", A2) - 1)
Method 2: RIGHT with LEN
Method 2: Use the total text length to return everything after the first space.
For the last name, we keep the part after the first space. LEN gives the total text length, and RIGHT returns the remaining characters after the first name has been accounted for.
This solves the second half of the challenge because the surname sits after the first space. By measuring the full text and subtracting the first-name portion, the formula can return only the last-name side into column C.
=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))
Method 3: TEXTSPLIT in newer Excel
Method 3: Split the name into multiple cells in one step with TEXTSPLIT.
If you are using a newer Excel version, TEXTSPLIT can split the name across columns automatically. It is not required for this validator, but it is a useful modern option when the worksheet supports it.
This solves the whole split in one move instead of building separate first-name and last-name formulas. It is especially useful when the worksheet supports modern spill formulas and the goal is to separate the name with as little setup as possible.
=TEXTSPLIT(A2, " ")
Function Explanation
1. SEARCH
SEARCH returns the position of one text fragment inside another. In this challenge, it finds the space that separates first and last name.
Learn more this functionSEARCH
2. LEFT
LEFT returns characters from the start of a string. Combined with SEARCH, it gives us the first name cleanly.
Learn more this functionLEFT
3. LEN
LEN counts the total number of characters in the full name. That total helps RIGHT determine how many characters belong to the last-name side.
Learn more this functionLEN
This pattern assumes each row has one first name and one last name separated by a single space. If the source data includes middle names or extra spacing, the cleanup logic usually needs an extra step.
Split each full name into first-name and last-name columns, then complete the audit summary so the worksheet shows how many rows were split successfully and how many total records are in the list.