Type the first name beside the first full name and press Ctrl+E, and Flash Fill splits the rest of the column. For formulas that keep up with changes, use TEXTBEFORE and TEXTAFTER, or LEFT, MID and FIND in older versions.
Point at a step to see the sheet right after it.
B2:B5 = Marco, Fatima, Kwame, Johan Flash Fill saw that Marco is the first word of Marco Rossi, and took the first word of every name.
A contact list, a CRM export or a sign-up form usually gives you one column of full names, and the mail merge, the sort by surname or the lookup you need wants first and last names apart. Typing them out is fine for ten rows and hopeless for a thousand. The two ways to reach for first are Flash Fill, which works out a rule from an example you type, and text formulas, which follow a rule you write.
Flash Fill is quicker, but the rule it picks may not be the one you meant. Formulas take longer to write and do exactly what they say, and they update when a name changes. Below you split a list both ways, then see each method taken apart on a small sheet, with the names that break a split: middle initials, one-word names, a space left on the end.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
Flash Fill looks at what you typed beside the first name and picks a rule that produces it. One example often fits more than one rule, and it can pick the wrong one: “everything after the first space” and “the last word” agree on Marco Rossi and disagree on Kwame J. Osei. A second example, on a name where they differ, settles it.
B2:B5 = Rossi, J. Osei, Khan, Berg One example fits “everything after the first space”, and that’s the rule Flash Fill picked, so Kwame’s initial came along.
Point at a step to see the sheet right after it.
Run it with Ctrl+E, on a Mac too, or Data → Flash Fill. Excel also offers it by itself
once you start typing a second example: the rest of the column appears in grey, and
Enter accepts it. Either way, read the column afterwards. Flash Fill writes values, not formulas, so
nothing on the sheet shows the rule it chose, and nothing updates when a name in column A changes.
Text to Columns is Excel’s menu command for this, and it cuts a column at every space in one go. Select the names and choose Data → Text to Columns. Pick Delimited and click Next, then tick Space and click Next. Set Destination to an empty column, because by default the split starts in the names’ own column and writes over them. Click Finish.
Like Flash Fill, it writes values, so nothing updates when a name changes. And because it cuts at every space, a name with a middle initial takes three columns instead of two. Witsheet doesn’t have Text to Columns yet, so try these steps in your own Excel.
Microsoft 365 and Excel 2024 have two functions made for this. =TEXTBEFORE(A2, " ") takes
the text before the first space. =TEXTAFTER(A2, " ", -1) takes the text after the last
one, because -1 counts from the end, so Kwame J. Osei’s surname comes out as Osei with nothing extra
in the formula.
B4 = Kwame Everything before the first space, so a middle initial makes no difference to the first name. Look at Kwame.
Give either one the whole column and one formula does every row: =TEXTBEFORE(A2:A200, " ") spills a first name down the sheet. Use them if everyone who opens the workbook has one of those
versions. In Excel 2021 and earlier they show #NAME?, which is when you need LEFT, MID
and FIND below.
=TEXTSPLIT(A2, " ") cuts a name at every space and spills the parts into the cells to its
right. It’s one formula per row, so write it once and copy it down. Given a whole column it can’t
spill a row for each name, and cuts the result short.
D3 = Khan Fatima has two spaces in a row, so there’s an empty part between them and Khan lands a column over. Kwame’s three parts put J. under Last for a different reason.
That makes it the right tool when you want every part, and the wrong one for a first-name and a
last-name column: a middle initial takes the surname’s place. If the cells to its right already hold
something, it shows #SPILL! instead of the parts. It’s at its best when every row has the
same parts, as =TEXTSPLIT(A2, ", ") does for a column of “London, UK”.
These formulas work in every version of Excel, and you’ll meet them in workbooks for years. LEFT takes characters from the start of the text, and FIND says how many: it
gives the position of the space, and the first name is everything before it. So =LEFT(A2, FIND(" ", A2)-1) reads “the characters before the space”, and works on every
row, whatever the name’s length.
B2 = Marco FIND finds the space at character 6, and LEFT takes the 5 before it. Each copy finds its own row’s space, so every name splits right.
MID takes characters from the middle of the text: where to start, and how many. The
surname starts one character after the space, and runs to the end of the name.
B2 = Rossi FIND puts the space at 6, so MID starts at 7 and takes up to 100 characters. It runs out at the end of the name, after Rossi.
=RIGHT(A2, LEN(A2)-FIND(" ", A2)) gets the same surname from the other end: the name’s
length minus the position of the space is how many characters the surname has. Use whichever reads
more clearly to you. SEARCH works in place of FIND in both: it ignores capitals
and accepts wildcards, which matters when you split at a word rather than at a space.
A name with three parts has two spaces, and both formulas above split at the first. The first name
is still right; the last name takes the middle part with it. FIND has an optional third
argument, where to start looking, and starting after the first space finds the second.
B2 = J.·Osei FIND stops at the first space it meets, so the middle initial comes along. Right for Marco Rossi below, wrong for Kwame.
That version needs a second space to find, so it’s only for the rows that have one. =TEXTAFTER(A2, " ", -1) handles both kinds of name as it stands, if your Excel has it.
FIND answers #VALUE! when the text it looks for isn’t there, and a list of
names always has someone without a space: a one-word name, a company, a typo.
B2 = #VALUE! Sumarni is the whole name, as it is for many people in Indonesia, so there’s no space and FIND answers #VALUE!. Every list long enough has one.
IFERROR works too, =IFERROR(LEFT(A2, FIND(" ", A2)-1), A2), but it would
also hide any other mistake in the formula. Adding the space fixes only the case you meant to fix.
TEXTBEFORE and TEXTAFTER answer #N/A here, and their last
argument says what to show instead: =TEXTBEFORE(A2, " ", , , , A2) keeps the whole name
as the first name, and =TEXTAFTER(A2, " ", -1, , , "") leaves the last name empty.
Not everything splits at a space. Codes such as order numbers, account codes and postcodes often
have parts of a fixed length, and then LEFT, MID and RIGHT need no FIND at all: the positions are the same on every row.
B2 = UK The first two characters: the country.
When the parts vary in length, split at the separator instead, the way the names split at the
space: =LEFT(A2, FIND("-", A2)-1) for everything before the first hyphen, or FIND("@", A2) to split an email address into the name and the domain. In Microsoft 365
and Excel 2024, =TEXTBEFORE(A2, "-") and =TEXTAFTER(A2, "@") say the same in
fewer characters.
To carry on in your own Excel, download the workbook. It’s the same job at full size: 120 contacts with the names that trip a split up (middle names, initials, one-word names), emails to split at the @, shipment codes to split by position, and six tasks with the answers on a sheet of their own.
120 contacts with full names, emails and shipment codes, including the middle names, initials and one-word names that break a split. Practice Flash Fill, LEFT, MID, RIGHT and FIND.
120 rows · Full Name · Email · Shipment
Free · fictional data · opens in Excel and Google Sheets
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches name splitting: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →