Blog / Text

How to split text in Excel: 6 easy methods

3 November 2026 · 6 min read

Split names, emails and codes into separate cells in Excel with Text to Columns, Flash Fill, TEXTSPLIT, TEXTBEFORE and classic formulas, with worked examples.

Splitting text is one of the most common clean-up jobs in Excel. A full name needs to become a first and last name, an email address needs its domain pulled out, or an imported product code needs breaking into parts so you can sort and filter by them.

Excel gives you several ways to do it. Some are one-off tools that change your data once; others are formulas that update automatically when the text changes. This guide covers all of them, shows exactly when to use each, and points out the mistakes that trip people up.

Which method should you use?

• Text to Columns: you want a quick, one-time split of a whole column, and you don't need it to update later.
• Flash Fill: the pattern is easier to show than to describe, such as pulling initials out of names.
• TEXTSPLIT: you have Microsoft 365 or Excel 2024, and want every part of the text spread across cells by one formula.
• TEXTBEFORE and TEXTAFTER: you want one specific part, such as the first name or an email domain, as a formula that updates itself.
• LEFT, MID, RIGHT and FIND: you need it to work in every version of Excel, or the text has no delimiter at all.

A delimiter is the character that separates the parts: a space, a comma, a dash, an @ sign or a line break.

Method 1: Text to Columns

Text to Columns splits every cell in a column at once, using a delimiter or fixed character positions.

1. Select the cells you want to split.
2. On the Data tab, in the Data Tools group, click Text to Columns.
3. Choose Delimited if a character separates the parts, or Fixed width if each part is always the same length. Click Next.
4. Tick the delimiter, such as Space or Comma, or type your own in Other. Tick Treat consecutive delimiters as one if there may be double spaces. Click Next.
5. Choose a Destination cell to the right of your data, so your original column is kept, then click Finish.

Text to Columns writes plain values, not formulas, so it will not update when the original text changes. It also overwrites whatever is in the columns to the right of the destination, so make sure they are empty first.

Method 2: Flash Fill

Flash Fill learns a pattern from an example you type.

1. In the column next to your data, type the result you want for the first row. For "Maria Garcia", type Maria.
2. Press Ctrl + E, or click Flash Fill on the Data tab.

Excel fills the rest of the column by following your example. Check a few rows, because Flash Fill guesses, and names with middle names or extra spaces can confuse it. Like Text to Columns, the results are fixed values.

Method 3: TEXTSPLIT

In Microsoft 365 and Excel 2024, TEXTSPLIT splits text into separate cells with a single formula:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty])

=TEXTSPLIT(A2, " ") splits "Maria Luisa Garcia" into three cells: Maria, Luisa and Garcia. The results spill to the right, so the cells next to the formula must be empty, or you will see a #SPILL! error.

A few useful variations:

• Several delimiters: =TEXTSPLIT(A2, {",", ";"}) splits at commas and semicolons.
• Line breaks: =TEXTSPLIT(A2, CHAR(10)) splits text that was typed on several lines in one cell.
• Into rows instead of columns: leave the column delimiter empty and give a row delimiter: =TEXTSPLIT(A2, , ",").
• Skip empty parts: set the fourth argument to TRUE, so double commas do not leave blank cells.

Method 4: TEXTBEFORE and TEXTAFTER

Often you only need one part of the text. TEXTBEFORE returns everything before a delimiter, and TEXTAFTER returns everything after it.

Result: Maria. Everything before the first space.

A third argument chooses which delimiter to split at. Use -1 for the last one, which gets a last name however many middle names come first:

Result: Garcia. -1 splits at the last space.

The same two functions split an email address into its user name and domain:

Result: contoso.com. TEXTBEFORE(A2,"@") would return jordan.lee.

When names are stored as "Last, First", split at the comma and remove the leftover space with TRIM:

Result: Maria. TRIM removes the space after the comma.

These formulas update automatically, so they are ideal for tables that get new rows.

Method 5: LEFT, MID, RIGHT and FIND (any version of Excel)

Before TEXTBEFORE existed, people combined FIND, which returns the position of a character, with LEFT, MID and RIGHT, which take characters from the start, middle or end. These work in every version of Excel.

To get the first name, take everything up to one character before the space:

Result: Maria. FIND returns 6, the position of the space, so LEFT takes 5 characters.

To get everything after the space, start one character after it and take plenty of characters; MID simply stops at the end of the text:

Result: Garcia. Asking for 100 characters is safe; MID returns only what is there.

Method 6: Split text without a delimiter

Some codes have no separator, but each part is always in the same position. Then LEFT, MID and RIGHT can split them by position alone. For the code "NY20260042", the city is the first 2 characters, the year the next 4, and the number the last 4:

Result: 2026. Start at character 3 and take 4 characters.

Text to Columns with Fixed width does the same job once, without formulas.

Common mistakes

• Extra spaces. Imported text often has spaces at the start or end, or double spaces in the middle, which leaves empty or misaligned parts. Clean it with TRIM first, or wrap your split formula in TRIM.
• The delimiter is missing. If a cell has no space, FIND returns #VALUE! and TEXTBEFORE returns #N/A. Wrap the formula in IFERROR to fall back to the whole cell: =IFERROR(TEXTBEFORE(A2," "),A2).
• Numbers turn into text. The results of LEFT, MID, RIGHT and TEXTBEFORE are always text, even when they look like numbers. Wrap them in VALUE if you need to calculate with them.
• Leading zeros disappear. Text to Columns turns "0042" into 42. In step 3 of the wizard, set that column's format to Text to keep the zeros.
• Data gets overwritten. Text to Columns and TEXTSPLIT both need empty cells to the right. Leave room, or choose a different destination.

Frequently asked questions

How do I split a cell into two columns in Excel? Select the column and use Text to Columns with your delimiter, or use TEXTBEFORE in one column and TEXTAFTER in the next.

How do I split text by a line break? Use =TEXTSPLIT(A2, CHAR(10)) in Microsoft 365. In Text to Columns, choose Other and press Ctrl + J in the box to enter a line break.

Can I split text with a formula in older versions of Excel? Yes. Combine LEFT, MID and RIGHT with FIND, as in Method 5. They work in every version.

Will my split update when the original text changes? Formulas such as TEXTSPLIT, TEXTBEFORE and LEFT update automatically. Text to Columns and Flash Fill do not.

Practice splitting text

Reading about these formulas is a start, but they only stick once you have typed them yourself. Our splitting text lesson gives you real names, emails and codes to split, and checks every answer instantly. For more detail on each function, see the guides to TEXTBEFORE and TEXTAFTER, LEFT, RIGHT and MID, FIND and SEARCH and TRIM.

Keep reading

• How to calculate age in Excel (years, months, days)
• SUMIF vs SUMIFS: what's the difference?
• VLOOKUP vs XLOOKUP vs INDEX MATCH: which to use?