How to split text in Excel
To split text in Excel, select the cells, choose Data > Text to Columns, pick Delimited and tick the character that separates the parts, such as a space or a comma. Excel puts each part in its own column. If you want the split to update when the data changes, use formulas instead. This guide shows three ways: the button, Flash Fill and formulas.
1. Split with Text to Columns
Select the column, go to Data > Text to Columns (Alt, A, E on Windows), choose Delimited and click Next. Tick the delimiter that separates your parts, for example Space for "Ana Cohen" or Comma for a list. Click Finish. The parts are written to the columns on the right, so make sure those are empty, because Excel overwrites them without asking.
Choose Fixed width instead when the parts always have the same length, such as a code where the first three characters are a region.
2. Let Flash Fill see the pattern
Type the first result next to the data, for example "Ana" beside "Ana Cohen". Start typing the second result and Excel shows a gray preview of the rest. Press Enter to accept, or press Ctrl+E to fill the column. Flash Fill is quick, but it is a one-off: the results do not change if the source changes.
3. Split with formulas: first name
Formulas update on their own. To take the first word, find the position of the space with FIND, then take everything to its left with LEFT. Subtract 1 so the space itself is not included.
Example: The first name
| A | B | C | |
|---|---|---|---|
| 1 | Full name | First name | |
| 2 | Ana Cohen | Ana |
The space is the fourth character, so LEFT takes 3 characters: Ana.
4. Split with formulas: last name and email
In Microsoft 365 and Excel 2024, TEXTAFTER and TEXTBEFORE do the job in one step: they return the text after or before a delimiter you name.
Example: The last name
| A | B | C | |
|---|---|---|---|
| 1 | Full name | Last name | |
| 2 | Ana Cohen | Cohen |
TEXTAFTER returns everything after the first space.
5. Names with a middle name
With a middle name, the first space is no longer the last one. Take the first name from the left as before. To get the last word, set the instance number of TEXTAFTER to -1, which counts from the end. The lesson on pulling out the first and last word shows the older method that works in every version.
Common mistakes
Splitting over data on the right. Text to Columns overwrites whatever is in the next columns.
Fix: Insert empty columns first, or move the data to a new sheet.
Extra spaces. A double space between words produces an empty part.
Fix: Run TRIM over the column first, then split.
A cell with no space. FIND returns #VALUE! when the delimiter is missing.
Fix: Wrap the formula in IFERROR, or use TEXTBEFORE with its if-not-found argument.
Frequently asked questions
How do I split text into rows instead of columns?
In Microsoft 365, TEXTSPLIT can spill the parts down the sheet. In older versions, split into columns first and then copy and paste with Transpose.
How do I combine text again?
Use the ampersand, as in =A2&" "&B2, or TEXTJOIN to join many cells with a separator.
Do Text to Columns and Flash Fill update automatically?
No. They write fixed values. Use formulas if the result must follow the source.
Related functions
- LEFTLEFT returns the first characters of a text, RIGHT the last ones.
- MIDMID returns characters from the middle of a text: a start position and a length.
- FINDFIND and SEARCH return the position of one text inside another.
- TEXTAFTERTEXTAFTER and TEXTBEFORE split text at a delimiter, such as the @ in an e-mail, without counting positions.
- TEXTJOINTEXTJOIN joins the text from a range with a separator such as a comma, and can skip empty cells.
- TRIMTRIM removes extra spaces from text: at the start, at the end, and doubles between words.
More on splitting text
The guide shows Text to Columns, Flash Fill and formulas. This piece adds how to deal with names that have a middle name, how to split a list into rows, and how to keep leading zeros when you split numbers.
Names with more than two words
A full name can have a middle name, a prefix or two last names. A rule that always takes the first word and the last word covers most cases. In Microsoft 365 and Excel 2024, a negative instance number counts from the end of the text.
=TEXTBEFORE(A2," ")
=TEXTAFTER(A2," ",-1)Names with two-word last names, such as "de la Cruz", cannot be split reliably by any rule. Check the result by eye before you use it.
Split a list into rows
Text to Columns always writes across. To get one item per row, use TEXTSPLIT in Microsoft 365 and give it a comma as the column separator or as the row separator. In older versions, split into columns, copy the result, and paste it with Paste Special > Transpose.
Keep leading zeros and dates intact
Text to Columns tries to be helpful: it turns "007" into 7 and "1-2" into a date. In the last step of the wizard, choose a column and set its data format to Text, and Excel keeps what you see. Check the format of the destination cells too, because a General cell converts values on entry.
Clean the text before you split it with TRIM. For the full set of tools, see [the data cleaning lesson](/learn/data-cleaning-tools) and [how to remove duplicates](/guides/remove-duplicates-in-excel).