Text to Columns vs TEXTSPLIT in Excel

Text to Columns vs TEXTSPLIT is a choice between a one-time command and a live formula. Text to Columns, on the Data tab, splits a column into several in a few clicks and leaves plain values behind; it works in every desktop version of Excel. TEXTSPLIT is a formula that spills the pieces into neighbouring cells and re-splits whenever the source changes, but it needs Microsoft 365, Excel 2024 or Excel for the web. This comparison helps you pick the right one for imports, exports and job-test tasks.

Text to Columns vs TEXTSPLIT at a glance#

Text to Columns changes your data. TEXTSPLIT leaves the original alone and shows the split version next to it.

How to use Text to Columns#

Text to Columns walks you through three steps. Here is the full sequence:

1. Select the cells to split, for example A2:A5. Select only one column.
2. Go to Data > Text to Columns (in the Data Tools group). On Windows you can press Alt, A, E. On a Mac, choose Data > Text to Columns.
3. Step 1: choose Delimited (split at a character such as a comma) or Fixed width (split at set positions). Click Next.
4. Step 2: tick the delimiter, such as Comma, and untick Tab if you don't need it. The Data preview shows the result. Tick Treat consecutive delimiters as one if there are doubled commas. Click Next.
5. Step 3: pick a Column data format for each column (General, Text, Date, or Do not import column (skip)). Set the Destination, for example $B$2, so the original column is kept. Click Finish.

If the destination cells aren't empty, Excel asks whether to replace the data. Our step-by-step guide to Text to Columns covers fixed-width splits and date formats.

How to use TEXTSPLIT#

TEXTSPLIT splits one cell at a delimiter and spills each piece into its own cell:

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

Only the first two arguments are required. row_delimiter splits into rows as well, ignore_empty set to TRUE skips empty pieces from doubled delimiters, and pad_with fills gaps when rows have different numbers of pieces. Our TEXTSPLIT guide has more examples.

The same split, side by side#

Here are four records exported as one text column:

With Text to Columns#

Select A2:A5, choose Delimited, tick Comma, set the destination to $B$2, and click Finish. Columns B, C and D now hold the name, department and city as plain values.

One catch: the delimiter is only the comma, so the department and city keep a leading space (" Sales"). Fix it with TRIM in a helper column, or tick Space too. Ticking Space would also split "Dana Lee" into two columns, so TRIM is usually the cleaner fix. See clean messy data with TRIM.

With TEXTSPLIT#

In B2:

=TEXTSPLIT(A2,", ")

The delimiter is a comma and a space, so the pieces come out clean: Dana Lee, Sales, London spill into B2:D2. Fill down to B5, and each row spills to the right. If you later paste a corrected list into column A, columns B to D update by themselves.

If some rows use a comma without a space, split on the comma and trim the pieces:

=TRIM(TEXTSPLIT(A2,","))

Text to Columns vs TEXTSPLIT on numbers and dates#

The biggest hidden difference is the type of the result. Text to Columns converts pieces that look like numbers or dates into real numbers and dates (with the General format). TEXTSPLIT returns text every time.

Take 1200;North;2026-10-01:

• Text to Columns gives the number 1200 and a real date you can sort and calculate with.
• TEXTSPLIT, with =TEXTSPLIT(A2,";"), gives the text "1200" and the text "2026-10-01". SUM ignores them. Wrap a piece in VALUE, or multiply by 1, to convert it.

The conversion can also hurt. Text to Columns turns 007 into 7 and may read 03-04 as a date. In step 3, set those columns to Text to keep them exactly as they are. Numbers stored as text explains the difference between the two.

When to use Text to Columns#

Use Text to Columns when you want to fix the data once and move on. It is the better choice when:

• It is a one-off import that won't be refreshed.
• You need fixed-width splits, such as old system exports with columns at set positions.
• You want real numbers and dates without extra formulas.
• The file goes to people on Excel 2021 or older.
• You want plain values that won't break if the original column is deleted.

When to use TEXTSPLIT#

Use TEXTSPLIT when the source text will change or come back. It is the better choice when:

• You paste in a new export every week and want the split columns to update by themselves.
• You want to keep the original column untouched for checking.
• You need to split into rows, or by several delimiters at once.
• You want to feed the pieces into other formulas, such as SORT or UNIQUE. See SORT and SORTBY.

Common mistakes with Text to Columns and TEXTSPLIT#

1. Overwriting data to the right. Text to Columns writes into the next columns. Insert empty columns first, or set a destination.
2. Losing leading zeros or getting surprise dates. Set those columns to Text in step 3.
3. Blocking TEXTSPLIT's spill. Anything in the cells to the right causes #SPILL!. See the #SPILL! error.
4. Forgetting TEXTSPLIT returns text. Convert numbers before you total them.
5. Using TEXTSPLIT inside a table. Spilling formulas don't work in Excel tables; put TEXTSPLIT next to the table. Excel table vs range explains this limit.
6. Selecting several columns for Text to Columns. It works on one column at a time.

Our recommendation#

For a single clean-up, use Text to Columns: it is fast, it converts types, and everyone's Excel has it. For anything you'll repeat, or when the original must stay intact, use TEXTSPLIT on Microsoft 365 or Excel 2024, and convert number pieces with VALUE.

If every piece has a fixed length, LEFT, RIGHT and MID work in any version; the LEFT function guide shows how. If you only need the first or last part, TEXTBEFORE and TEXTAFTER are simpler than either; see TEXTBEFORE and TEXTAFTER and how to split first and last names. If the pattern is irregular, Flash Fill vs formulas compares a third option.

In short: Text to Columns to split once, TEXTSPLIT to keep splitting.

Practise it#

Split the records with TEXTSPLIT in the exercise on this page. Then practise TEXTSPLIT and spill references in the modern Excel functions lesson, and splitting at one delimiter in the TEXTBEFORE and TEXTAFTER lesson.