Excel Split Function: How to Split Text into Columns

Formulas & functions · Updated November 22, 2026

In Excel for Microsoft 365, the function for splitting a text string is TEXTSPLIT. It separates text at a delimiter, such as a comma or space, and spills the pieces into neighboring cells; for example, it can turn North,West into two cells containing North and West.

What TEXTSPLIT does

TEXTSPLIT takes text from a cell or a typed string and divides it wherever it finds a delimiter you specify. A delimiter is the character or sequence of characters marking a boundary: a comma in a comma-separated list, a space between words, or a line break between entries. The function is useful when imported data arrives as several values packed into one cell.

The result is a dynamic array. Instead of returning just one value to its formula cell, the formula can fill adjacent cells with all the separated pieces. Excel calls this spilling. If the formula is in B2 and returns three parts, the values appear in B2, C2, and D2, provided those cells are clear. The formula itself remains only in B2.

TEXTSPLIT is available in Microsoft 365 and Excel 2024. It is not included in older perpetual versions such as Excel 2019 or Excel 2016, so an older installation may show #NAME? for the function. The explanation and examples below use Excel for Microsoft 365 on Windows.

TEXTSPLIT syntax and arguments

The function accepts text, a column delimiter, and several optional settings. Its syntax is:

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

  • text is the string to divide, often a cell reference such as A2.
  • col_delimiter marks where Excel should split into separate columns. It can be a character or a longer string.
  • row_delimiter, if supplied, marks where Excel should split into separate rows.
  • ignore_empty controls whether adjacent delimiters or delimiters at the ends create empty results. Its default is FALSE.
  • match_mode controls whether delimiter matching is case-sensitive. The default, 0, is case-sensitive; 1 makes it case-insensitive.
  • pad_with supplies a value for missing positions when a two-direction split produces rows of unequal length. The default is #N/A.

Square brackets in the syntax indicate optional arguments; do not type the brackets into a formula. If you want to set a later optional argument but leave an earlier one at its default, leave its position blank between commas. That is why formulas with omitted arguments can contain consecutive commas.

Split a string into columns

For a straightforward example, suppose A2 contains East,Central,West. With the text in A2, use this formula in B2 to split at each comma:

=TEXTSPLIT(A2,",")

Excel spills East, Central, and West across B2:D2. The original value in A2 stays unchanged. To split on a space instead, with A2 containing Mina Patel, enter =TEXTSPLIT(A2," ") in B2; the result is Mina in B2 and Patel in C2. The delimiter must match the character actually present in the source. A regular space is not the same as a nonbreaking space, which can appear in copied web data.

You can split using a delimiter made of more than one character. For example, if A2 contains item||price||stock, then, with that string in A2, enter =TEXTSPLIT(A2,"||") in B2. The results are item, price, and stock. Excel looks for the complete delimiter string, rather than splitting at each individual vertical bar.

Split text into rows or both rows and columns

Use the third argument, row_delimiter, when each item should appear on a new row. Suppose A2 contains Jan;Feb;Mar. With the source string in A2, enter this in B2:

=TEXTSPLIT(A2,,";")

The second argument is intentionally blank, so Excel does not use a column delimiter. The semicolon is the row delimiter, and the result spills down B2:B4 as Jan, Feb, and Mar. This is handy when a cell contains a list that you want to turn into a vertical list for later sorting or analysis.

You can provide both delimiters to create a grid. With Red,Blue;Green,Yellow in A2, enter =TEXTSPLIT(A2,",",";") in B2. Commas separate columns and semicolons separate rows, producing a two-by-two grid: Red and Blue on the first row, Green and Yellow on the second. Make sure the cells in the full output area are empty; an occupied cell in the spill area can prevent the formula from returning its complete result.

Handle repeated delimiters and different separators

By default, every delimiter marks a split, including repeated delimiters. With red,,blue, in A2, a comma split includes empty results where delimiters are consecutive or at the end. If those empty pieces are not meaningful, set ignore_empty to TRUE. With the example string in A2, use this formula in B2:

=TEXTSPLIT(A2,",",,TRUE)

The resulting nonempty pieces are red and blue. This setting is useful for inconsistent lists, but it also removes intentional empty fields. If an empty field represents a missing value in a data file, keep the default FALSE so the positions of later values remain clear.

When a source uses more than one possible separator, pass an array of delimiters. Suppose A2 contains Ada Lovelace,London, where either a space or a comma marks a boundary. With that text in A2, enter this formula in B2:

=TEXTSPLIT(A2,{","," "},,TRUE)

The array constant lists the comma and space as alternatives; ignore_empty is TRUE to avoid an empty result where the comma is followed by a space. The spill is Ada, Lovelace, and London. In some regional settings, Excel uses different separators in formulas and array constants. If a copied formula is rejected, check the list and array separators configured for that Excel installation.

Common errors and practical fixes

A frequent problem is #SPILL!. The formula may be correct, but Excel cannot place the full result because one or more destination cells are occupied, merged, or otherwise obstructed. Clear the spill range, or move the formula to an area with enough open cells. Selecting the error indicator can help identify the obstruction.

#NAME? usually means Excel does not recognize the function name. Check that the workbook is open in Microsoft 365 or Excel 2024 and that the function is spelled TEXTSPLIT. In an unsupported version, use Text to Columns, Power Query, or a formula method supported by that version instead.

If a split appears not to work, confirm the exact delimiter. A common cause is hidden or unexpected characters: data copied from a website may contain nonbreaking spaces, and line breaks are not ordinary spaces. If the source uses line breaks within a cell, Excel represents a line feed with CHAR(10). With line-break-separated entries in A2, this formula in B2 splits at each line feed:

=TEXTSPLIT(A2,CHAR(10))

Also check for leading or trailing spaces in the results. TEXTSPLIT separates text; it does not automatically trim every returned item. If cleanup is needed, apply a suitable text-cleanup step to the results rather than assuming the delimiter operation removed extra spaces.

TEXTSPLIT compared with other Excel methods

TEXTSPLIT is usually the most direct choice for a formula-driven split in a modern Excel workbook. Its results update when the source text changes, and the spill range expands or contracts as needed. That makes it useful for repeatable worksheets where the source cell may be replaced or refreshed.

For a one-time split, the ribbon command may be simpler. Select the source cells, then choose Data > Text to Columns. In the wizard, choose Delimited if a character such as a comma separates values, or Fixed width if the boundaries are at set positions. Choose the delimiter and preview the result before finishing. Text to Columns writes results into cells rather than creating a formula that recalculates from the source. Choose a destination area carefully, since the operation can overwrite existing data. The command is also available in many older Excel versions.

Power Query is a better fit for recurring imports or larger cleanup workflows. It can split a column by a delimiter as part of a query that you refresh, and it offers a sequence of transformation steps rather than a single worksheet formula. For a quick, transparent split of one or a few cells, that extra setup may be unnecessary. For a repeatable process involving imported files, it can be easier to maintain.

Frequently Asked Questions

What is the Excel function for splitting text?
Use TEXTSPLIT in Microsoft 365 or Excel 2024. Give it the source text and the delimiter, and it returns the separated pieces as a spilled array.

How do I split a cell into separate columns?
With the text in A2 and commas between its values, enter =TEXTSPLIT(A2,",") in an empty cell. The results spill horizontally into adjacent cells.

Can TEXTSPLIT split text into rows?
Yes. Leave the column-delimiter argument blank and provide a row delimiter as the third argument. For semicolon-separated values in A2, use =TEXTSPLIT(A2,,";") to spill the values down rows.

Why does TEXTSPLIT return #SPILL?
Excel cannot place the entire result in the cells it needs, often because a cell in the spill area already contains data. Clear the obstructing cells or move the formula to an open area.

What can I use instead of TEXTSPLIT in older Excel?
For a manual split, use Data > Text to Columns. For repeatable imports, consider Power Query. Both are alternatives when the installed Excel version does not support TEXTSPLIT.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.