=TEXTBEFORE(A2," ") returns everything before the first space, so Ana Silva gives Ana, and =TEXTAFTER(A2," ") returns everything after it, Silva. Fill both formulas down and every full name is split into two columns.
| A | B | C | |
|---|---|---|---|
| 1 | Full name | First | Last |
| 2 | Ana Silva | Ana | Silva |
| 3 | Ben Okafor | Ben | Okafor |
| 4 | Chen Wu | Chen | Wu |
| 5 | Dara Kelly | Dara | Kelly |
| 6 | Eli Novak | Eli | Novak |
Change a name in column A and the two columns follow. TEXTBEFORE, TEXTAFTER and TEXTSPLIT need Microsoft 365 or Excel 2024. For Excel 2021 and older, the LEFT and FIND version is further down.
TEXTSPLIT: split a cell into columns
=TEXTSPLIT(A2," ") cuts the text at every space and spills each piece into its own cell. One formula fills as many columns as there are pieces:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Text | ||||
| 2 | Ana Maria Silva | Ana | Maria | Silva | |
| 3 | Paris, Lyon, Nice | Paris | Lyon | Nice |
- The delimiter can be more than one character:
", "splits at a comma followed by a space, so the pieces have no leading space. - Leave space for the result. If a cell to the right is not empty, the formula shows #SPILL! instead.
- The pieces are always text, digits included, so SUM skips them. Put two minus signs in front,
=--TEXTSPLIT(A2,","), when you need numbers.
The third argument is a row delimiter. With both, TEXTSPLIT builds a small table from one cell: commas separate the columns and semicolons the rows.
| A | B | |
|---|---|---|
| 1 | Teams | |
| 2 | Ana,Red;Ben,Blue;Chen,Green | |
| 3 | ||
| 4 | Ana | Red |
| 5 | Ben | Blue |
| 6 | Chen | Green |
Two more forms that work in Excel, shown here as text:
=TEXTSPLIT(A2,,", ") one piece per row: the column delimiter is left out
=TEXTSPLIT(A2,{";",","}) several delimiters in braces: splits at ; or ,
The first turns excel, formulas, text into a column of three cells, which is how you turn a list typed in one cell into rows. The second turns red;green,blue into red, green, blue.
Middle names and "Last, First"
TEXTAFTER(A2," ") returns everything after the first space, so for Ana Maria Silva it returns Maria Silva. To get the last word, count from the end with an instance number of -1. For names written as Silva, Ana, split at the comma and space instead:
| A | B | C | |
|---|---|---|---|
| 1 | Full name | First | Last |
| 2 | Ana Maria Silva | Ana | Silva |
| 3 | Ben Okafor | Ben | Okafor |
| 4 | Chen Li Wu | Chen | Wu |
| 5 | Cher | Cher | |
| 6 | |||
| 7 | Last, First | First | Last |
| 8 | Silva, Ana | Ana | Silva |
When the delimiter is missing, TEXTBEFORE and TEXTAFTER return #N/A, and a one-word name such as Cher has no space. Row 5 uses the sixth argument, the value to return instead: =TEXTBEFORE(A5," ",,,,A5) returns the whole name and TEXTAFTER(...,"") returns an empty cell.
| A | B | C | |
|---|---|---|---|
| 1 | User | Domain | |
| 2 | ana.silva@coddy.tech | ana.silva |
Your turn: In C2, return the domain of the email address in A2: everything after the @.
Separate names in older Excel
Without TEXTBEFORE and TEXTAFTER, find the position of the space with FIND and cut around it with LEFT, MID and RIGHT:
| A | B | C | |
|---|---|---|---|
| 1 | Full name | First | Last |
| 2 | Ana Silva | Ana | Silva |
| 3 | Ben Okafor | Ben | Okafor |
| 4 | Chen Wu | Chen | Wu |
FIND(" ",A2)is the position of the space: 4 inAna Silva.LEFT(A2,4-1)takes the 3 characters before it.MID(A2,4+1,100)takes up to 100 characters starting after the space. 100 is just "more than any name";=RIGHT(A2,LEN(A2)-FIND(" ",A2))gives the same result with an exact length.
These formulas return #VALUE! when there is no space. Wrap them in IFERROR, =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2), to return the whole cell instead.
| A | B | |
|---|---|---|
| 1 | Code | Prefix |
| 2 | KT-1043-BLUE |
Your turn: Return the part of the product code before the first dash in B2, using LEFT and FIND.
Text to Columns and Flash Fill
To split a column once, without formulas:
- Select the column of full names. Make sure the columns to its right are empty, because Text to Columns overwrites them.
- Data > Text to Columns.
- Choose Delimited, click Next, tick Space (or Comma, or type another character in Other), click Next.
- The first piece replaces the full names. To keep them, set Destination to the empty cell to their right, then click Finish.
Flash Fill is quicker for a pattern: type Ana next to Ana Silva, press Enter, then Ctrl+E or Data > Flash Fill, and Excel fills the first names of the other rows. Both tools write fixed values. If the names change later, the split does not; a formula does.
Frequently Asked Questions
How do I separate first and last names in Excel?
With a name like Ana Silva in A2, =TEXTBEFORE(A2," ") gives Ana and =TEXTAFTER(A2," ",-1) gives Silva, even when there is a middle name. In Excel 2021 and older use =LEFT(A2,FIND(" ",A2)-1) for the first name.
Which Excel versions have TEXTSPLIT?
TEXTSPLIT, TEXTBEFORE and TEXTAFTER are in Microsoft 365, Excel for the web and Excel 2024. Excel 2021 and older show #NAME?; use LEFT, MID, RIGHT and FIND there, or Data > Text to Columns.
How do I split a cell by a comma in Excel?
=TEXTSPLIT(A2,", ") spills each piece into its own column. To put the pieces in rows instead, leave the column delimiter empty: =TEXTSPLIT(A2,,", ").
Why does TEXTBEFORE return #N/A?
The delimiter is not in the cell, for example a single name with no space. Give a value for that case in the sixth argument: =TEXTBEFORE(A2," ",,,,A2) returns the whole cell when there is no space.
