How to Split Columns in Excel (Text to Columns, Flash Fill and TEXTSPLIT)

Last updated on November 10th, 2026 at 10:08 am

Excel’s Data > Text to Columns splits a selected column: choose Delimited, tick the separator (such as a comma or space), and select Finish. With Flash Fill, type the first result next to your data and press Ctrl+E. In Excel 365 and 2024, the TEXTSPLIT function splits with a formula that updates automatically. All of this works from Excel 2016 on, including Mac and the web. To join columns again, see how to concatenate in Excel.

Three ways to split one column into several in Excel
Pick a one-time split or a live formula. A drawn overview rather than a real Excel window.

Text to Columns overwrites cells to the right

Text to Columns writes the results into the columns to the right and overwrites anything there. Insert enough empty columns first: right-click the column letter to the right and choose Insert. Make a copy of the sheet if the data is important. The quickest way to make that room is to add a column in Excel for each piece the split will create.

Method 1: Text to Columns

  1. Select the column, or just the cells, you want to split.
  2. Go to Data > Text to Columns.
  3. Choose Delimited if a character separates the parts, such as “Smith, John” or “Red;Blue;Green”. Choose Fixed width if each part is a set number of characters, like product codes. Select Next.
  4. Tick the delimiter: Tab, Semicolon, Comma, Space or Other (type the character, such as – or /). Tick Treat consecutive delimiters as one if there are double spaces. Check the preview and select Next. If each part sits on its own line inside the cell, tick Other and press Ctrl + J in its box, a method covered in working with line breaks in an Excel cell.
  5. For each column in the preview, choose a format. Pick Text for codes with leading zeros, such as ZIP codes. Set the Destination if you want the results somewhere else. Choosing Text is also handy for account numbers in tables you pull out of a PDF into Excel, where converters often squeeze two values into one column.
  6. Select Finish.

Can I undo Text to Columns?

Yes, press Ctrl+Z straight away.

Related: cleaning data in Excel and sorting by last name once the names are split.

Method 2: Flash Fill

  1. In the column next to your data, type what you want from the first row. For example, type John next to “John Smith”.
  2. Press Enter, then press Ctrl+E (or Data > Flash Fill).
  3. Excel fills the rest of the column by following your pattern. Repeat in the next column for the last names.

Flash Fill works best with consistent data. Check a few rows; if it guesses wrong, type a second example and press Ctrl+E again. Names are the most common case, and separating names into two columns covers the tricky ones. Flash Fill also shows up on the Auto Fill Options button after you drag, as covered in using the fill handle in Excel.

Method 3: TEXTSPLIT (Excel 365 and 2024)

=TEXTSPLIT(A2,",")

This splits A2 at every comma and spills the parts into the cells to the right. Use ” “ for spaces, or a list of delimiters like {“,”,”;”}. Add TRIM to remove extra spaces: =TRIM(TEXTSPLIT(A2,”,”)). Because it’s a formula, the results update when A2 changes. Splitting on a space instead breaks a sentence into one word per cell, which is the first stage when you create a word cloud in Excel. If stray spaces remain after splitting, here is how to remove extra spaces in Excel.

To take just one part, use TEXTBEFORE or TEXTAFTER: =TEXTBEFORE(A2,” “) returns everything before the first space.

Method 4: Formulas for older Excel

  • First word: =LEFT(A2,FIND(” “,A2)-1)
  • Everything after the first space: =MID(A2,FIND(” “,A2)+1,100)
  • Last 4 characters: =RIGHT(A2,4)

Method 5: Power Query for repeat jobs

If you split the same kind of file every week, select the data, go to Data > From Table/Range, right-click the column in Power Query and choose Split Column > By Delimiter. Select Close & Load. Next time, paste the new data and select Refresh.

Common examples

Overwrite warnings and lost zeros

“There’s already data here. Do you want to replace it?”

Text to Columns would overwrite cells. Select Cancel, insert empty columns, and try again.

Leading zeros disappeared

Set the column format to Text in step 5 of Text to Columns.

Text to Columns is greyed out or shows an error

It’s greyed out while you’re editing a cell or when the sheet is protected; press Esc or unprotect the sheet. If you selected several columns, Excel warns that it can only convert one column at a time, so select a single column.

Can I split one merged cell?

Excel can’t split a single, unmerged cell into two. If cells are merged, select them and choose Home > Merge & Center to unmerge.