Excel Split Function: Using TEXTSPLIT to Separate Text

Formulas & functions · Updated November 22, 2026

If you mean the split function in Excel, use TEXTSPLIT in Microsoft 365 or Excel 2024 to divide text at a delimiter, such as a comma or space. Excel does not have a worksheet function named SPLIT; in older versions, use Text to Columns or a formula-based alternative.

What Excel’s split function does

Splitting text means breaking one text value into separate pieces wherever a chosen character or text string appears. For example, splitting red,green,blue at each comma returns three values: red, green, and blue. This is useful when imported or manually entered data combines several fields in one cell.

Excel’s modern function for this task is TEXTSPLIT. It can place the results across columns, down rows, or in a two-dimensional range when the text contains both column and row delimiters. The results are dynamic: if the source text changes, Excel recalculates the split, and the returned values can expand into neighboring cells.

That behavior is different from Text to Columns, which writes the separated values into cells as a one-time conversion. Use a formula when the original text may change and you want the result to update. Use Text to Columns when you want to transform a fixed column of data without maintaining a formula.

How TEXTSPLIT works

The basic syntax is TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). The text argument is the value or cell to split. The column delimiter determines where Excel starts a new column; the optional row delimiter determines where it starts a new row. The remaining arguments control consecutive delimiters, whether matching is case-sensitive, and what to return for uneven rows.

For example, with the text in A2, enter this formula in B2 to split a comma-separated value across columns:

=TEXTSPLIT(A2,",")

If A2 contains Maya,Noah,Liam, the formula returns Maya in B2, Noah in C2, and Liam in D2. The formula itself stays in B2; the other values occupy the spill range. Leave those destination cells empty so Excel can display the full result.

A delimiter can be more than one character. With the text in A2 separated by a vertical bar and spaces, such as North | Central | South, use:

=TEXTSPLIT(A2," | ")

Including the spaces in the delimiter removes them along with the separator. If you split only on the bar character, spaces adjacent to the bar may remain in the returned values. You can clean surrounding spaces with TRIM if necessary.

Split text into rows or columns

By default, TEXTSPLIT uses its column delimiter, so a simple split spreads results horizontally. To split downward instead, leave the column-delimiter argument empty and supply a row delimiter. With semicolon-separated text in A2, enter:

=TEXTSPLIT(A2,,";")

If A2 contains apples;pears;plums, the results spill into three rows below the formula. The two consecutive commas are intentional: they leave the column delimiter blank so the semicolon is interpreted as the row delimiter.

You can use both delimiter arguments when the source contains two kinds of structure. For instance, suppose A2 contains Item,Price;Pen,2;Book,5, where commas separate fields and semicolons separate records. With the value in A2, this formula returns a two-column, three-row array:

=TEXTSPLIT(A2,",",";")

The result is Item and Price on the first row, Pen and 2 on the second, and Book and 5 on the third. This can be handy for compact, consistently structured text. It is not a complete replacement for importing a properly formatted CSV file, especially if that file contains quoted fields with commas inside them.

Empty values, spaces, and matching options

Repeated delimiters can create empty results. For example, splitting red,,blue at commas normally preserves the empty value between the two commas. To ignore empty pieces, set the fourth argument to TRUE. With the text in A2, use:

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

This returns red and blue, leaving out the empty segment. The option is useful when source data has inconsistent repeated separators, but consider whether an empty field has meaning before discarding it. In a record where each position represents a particular field, an empty value may indicate missing information rather than noise.

Spaces are not automatically removed just because the delimiter is a comma. If A2 contains red, blue, splitting on the comma returns a leading space before blue. With that value in A2, wrap the result in TRIM:

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

In current Excel, the array result is cleaned element by element. TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one. If the source contains nonbreaking spaces from a web page, TRIM may not remove them; the source may need a separate cleanup step.

The optional match-mode argument controls case sensitivity when matching the delimiter. Its default is case-sensitive. This distinction matters mainly for text delimiters that contain letters, not common separators such as commas or pipes. The final optional argument, pad_with, supplies a value for missing positions when rows have different numbers of pieces; by default, Excel uses an #N/A value in those unmatched positions.

Choosing a delimiter and preparing data

The most important decision is choosing a separator that marks a real boundary in the source. A comma works for simple lists, but it can be unsafe for names or descriptions that may themselves contain commas. A consistent tab, pipe, semicolon, line break, or distinctive multi-character marker may be more reliable. If the delimiter also appears within a field, a basic split can break that field incorrectly.

  • Check several source rows before applying a formula. A separator that looks consistent in one example may vary elsewhere.
  • Decide whether empty pieces should be retained. They can represent missing fields or accidental repeated separators.
  • Confirm whether the output should run across columns or down rows, and make room for the spill range.
  • Keep a copy of the original column before using a one-time conversion such as Text to Columns.

For example, a list of full names separated by commas is not necessarily safe to split at every comma: a name such as Jordan, Lee might be intended as one value or as last name and first name. Likewise, an address can contain commas within a single address field. The function follows the delimiter you specify; it cannot infer the intended meaning of your data.

Older Excel versions and non-formula options

TEXTSPLIT is available in Microsoft 365 and Excel 2024. If it is not recognized in your edition, use Text to Columns for a fixed conversion. In Excel for Microsoft 365 on Windows, select the cells, then choose Data > Text to Columns. Choose Delimited, select the separator, review the preview, and finish the wizard. The command is also available on Excel for Mac, though ribbon layout can vary by release.

Text to Columns changes the selected data into separate columns rather than creating a formula that updates from the original. Set a destination if you need to preserve adjacent information; otherwise, the output may overwrite existing cells. For a one-off cleanup, this is often simpler than building a formula.

Older Excel versions can also use functions such as LEFT, MID, RIGHT, FIND, and LEN, but these generally require a separate formula for each piece and more care when the number of pieces varies. Power Query is another option for repeatable imports and transformations; it can split a column by a delimiter and refresh the transformation when updated source data is loaded. The best method depends on whether the task is a dynamic worksheet calculation, a one-time edit, or a recurring data-import process.

Common problems and fixes

An #SPILL! error usually means Excel cannot place the returned array because one or more target cells are occupied, merged, or otherwise blocking the spill range. Clear the cells where the results should appear, or move the formula to an area with enough room. Dynamic array formulas also cannot spill from within an Excel table; place the formula outside the table or use another approach suited to the table.

If the output stays in one piece, check that the delimiter exactly matches the source. A regular space and a nonbreaking space can look alike but are different characters. Similarly, a delimiter of comma-plus-space will not match a comma with no following space. If some rows split and others do not, inspect the source values for inconsistent separators.

If you get #NAME?, check the spelling and whether your Excel version includes TEXTSPLIT. In installations that use regional formula settings, the list separator between function arguments may differ from the comma shown in examples; use the separator required by that installation. Do not confuse argument separators with the text delimiter inside quotation marks.

A result that shifts neighboring data is usually not a calculation error: it can occur when a dynamic array expands after the source changes. Reserve enough blank space for the largest expected result, or place the formula on a separate sheet. If the number of split pieces varies, downstream formulas should account for the possibility that the spill range changes size.

Frequently Asked Questions

Is there a SPLIT function in Excel?
Not as a worksheet function named SPLIT. The equivalent modern function is TEXTSPLIT, available in Microsoft 365 and Excel 2024. In older editions, use Text to Columns or construct formulas with other text functions.

How do I split a cell at a comma?
If the text is in A2, enter =TEXTSPLIT(A2,",") in an empty cell. Excel returns the comma-separated pieces across columns. Make sure the cells to the right are clear so the result can spill.

Can TEXTSPLIT return results down a column?
Yes. Leave the column delimiter blank and supply the row delimiter as the third argument. For semicolon-separated text in A2, use =TEXTSPLIT(A2,,";") to return one piece per row.

How do I split text at a space?
With the text in A2, use =TEXTSPLIT(A2," "). This splits at each ordinary space, so repeated spaces can produce empty pieces unless you set the ignore_empty argument to TRUE. It may also separate words that should remain together.

Why does Excel show #SPILL! after TEXTSPLIT?
The returned values need a clear range of cells, but something is blocking that range. Clear or move the blocking content, unmerge any cells in the intended spill area, and make sure the formula is not inside an Excel table.

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.