Excel Split Function: How TEXTSPLIT Returns an Array

Formulas & functions · Updated November 22, 2026

Excel does not have a worksheet function named SPLIT; in Microsoft 365 for Windows, use TEXTSPLIT to divide text at a delimiter and return the pieces as a dynamic array. Enter the formula once, and Excel spills the results into neighboring cells automatically.

Does Excel Have a SPLIT Function?

The short answer is no: Excel’s built-in function for splitting text is TEXTSPLIT. The name “SPLIT” is often used informally, or because Google Sheets has a function with that name. The two spreadsheet programs have related ideas but different function names and syntax, so a Google Sheets formula copied directly into Excel may not work.

TEXTSPLIT is useful when a cell contains multiple pieces of information separated by a consistent character or string, such as a comma, pipe, semicolon, or line break. It can return those pieces across columns, down rows, or in a two-dimensional arrangement. Unlike a manual copy-and-paste operation, the results update when the source text changes.

The function is available in Microsoft 365 and Excel 2024. It is also available in Excel for the web with a supported Microsoft 365 account. If a workbook is opened in an older Excel version that does not recognize the function, the formula may display as #NAME?.

How TEXTSPLIT Returns an Array

TEXTSPLIT looks for a delimiter in a text value, divides the value wherever it finds that delimiter, and returns each resulting piece as an individual value. The returned values form a dynamic array: one formula occupies the cell where it was entered, while the other results spill into adjacent cells.

For example, with the text Harper,Lee,North in cell A2, and the source text in A2, enter =TEXTSPLIT(A2,",") in B2. The result spills horizontally into B2:D2 as Harper, Lee, and North. The comma is the column delimiter in this example. If the text in A2 later changes to Harper,Lee,South, the spilled result updates without rebuilding the formula.

That spill behavior is why TEXTSPLIT is described as an array-producing function. You do not need to select several output cells first or confirm the formula as a legacy array formula. Enter it in one cell and leave enough empty space in the direction the results need to spill. The top-left result remains associated with the formula cell; the other results are calculated outputs, not separate formulas.

Split Text Into Columns or Rows

The second argument identifies the column delimiter: the character or text string that separates pieces placed side by side. To split downward into rows instead, provide a row delimiter as the third argument. When both delimiters are supplied, TEXTSPLIT can use them to create a two-dimensional result.

  • Across columns: With red|blue|green in A2, and the source text in A2, enter =TEXTSPLIT(A2,"|") in B2. The three colors spill across columns B:D.
  • Down rows: With red|blue|green in A2, and the source text in A2, enter =TEXTSPLIT(A2,,"|") in B2. The omitted column-delimiter argument leaves the third argument available for the row delimiter; the colors spill down B2:B4.
  • Across columns and down rows: With Name:Jordan;Age:31 in A2, and the source text in A2, enter =TEXTSPLIT(A2,":",";") in B2. The colon divides entries across columns and the semicolon starts a new row, producing a two-column result.

These examples assume English-language Excel syntax with commas between function arguments. In an installation configured for another regional list separator, Excel may display or expect a different argument separator. That setting is distinct from the delimiter inside quotation marks: the comma in "," is the character being searched for in the text.

Use More Than One Delimiter

Real data is not always consistent. One row might use a comma while another uses a semicolon. TEXTSPLIT can accept an array of delimiters, allowing it to split at any of the listed characters. With apple,pear;plum in A2, and that text in A2, enter =TEXTSPLIT(A2,{",",";"}) in B2. The result is apple, pear, and plum in adjacent columns.

This option is useful for cleaning lists collected from different sources, but it is worth checking the delimiter choices before using it. If a listed character is also meaningful inside a data value, TEXTSPLIT will still treat it as a separator. For example, splitting on a comma will divide a place name such as Portland, Oregon unless the source format provides another way to protect that comma.

TEXTSPLIT can also search for a delimiter made of multiple characters. If the exact separator is two characters, such as ::, use that full string as the delimiter rather than splitting on each colon individually. This matters when individual delimiter characters may appear in the values themselves.

Empty Pieces, Spaces, and Uneven Data

By default, TEXTSPLIT preserves empty pieces created by repeated delimiters. For instance, red,,blue contains an empty value between the two commas. With this text in A2, and the source in A2, =TEXTSPLIT(A2,",",,TRUE) tells Excel to ignore empty results. The output then contains red and blue next to each other.

Ignoring empty pieces is not always the right choice. In a record where each position has a meaning—such as first name, middle name, and surname—an empty middle-name field may be important. Removing it shifts later values into different positions. Keep empty results when column position carries meaning; ignore them when repeated separators are simply accidental clutter and the positions do not matter.

Splitting does not automatically remove spaces that are part of the text around a delimiter. If A2 contains Mia, Boston, the second result begins with a space. With the source text in A2, enter =TRIM(TEXTSPLIT(A2,",")) in B2 to remove leading and trailing ordinary spaces from each returned piece. TRIM also reduces repeated internal spaces to a single space, so avoid it when those spaces are intentional.

TEXTSPLIT returns text pieces; a value that looks like a number or date may not automatically become a numeric value or a true Excel date just because it has been separated. If a later calculation depends on the result being numeric, verify the value type and convert it deliberately. This distinction can explain why a result that looks like “31” does not behave as expected in a calculation.

Dynamic Array Behavior and Common Errors

A common problem is #SPILL!. It means Excel cannot place the full array in the required output range. Check the cells where the result should appear for existing values, formulas, merged cells, or other obstructions, then clear or move whatever is blocking the spill. The formula cell itself must also be outside the blocked portion of the output.

Spilled results need open worksheet space, and dynamic array formulas cannot spill within an Excel table. If TEXTSPLIT is being used to process table data, put the formula in a regular worksheet range outside the table and refer to the source cell, or use another approach suited to the table workflow. In current Microsoft 365 versions, clicking the formula cell also makes it easier to see the outline of the intended spill range.

#VALUE! can occur when the result has an invalid shape or the requested output cannot be formed as expected. An empty source, unusual delimiter arrangement, or a mixture of row and column splits that creates uneven rows can deserve a closer look. When combining row and column delimiters, inspect the source structure and decide how missing positions should be represented. TEXTSPLIT provides a padding option for uneven results, but the default padding is an error value; choose a padding value only when it makes sense for the data.

If the formula shows #NAME?, first check the spelling of TEXTSPLIT and whether the Excel version supports it. If you see the formula text rather than a result, confirm that the cell is not formatted as Text and that the entry begins with an equals sign. Changing the format to General and re-entering the formula may resolve that case.

TEXTSPLIT Compared With Other Ways to Split

TEXTSPLIT is a good fit when the split should remain linked to its source and update automatically. It is concise for repeatable worksheet calculations and can split in both directions. Its main limitation is that it depends on a supported Excel version and needs a clear spill area.

For a one-time split, Excel’s Text to Columns feature is another option. In Excel for Windows, select the source cells and choose Data > Text to Columns, then follow the wizard to choose a delimiter or fixed-width layout. This writes the separated values into cells rather than creating a formula that recalculates from the source. Check the destination range first so existing data is not overwritten.

For recurring imports or larger cleaning tasks, Power Query may be more suitable. It can split a column during a repeatable import-and-transform process, then refresh the results when the source data changes. It requires setting up a query rather than entering a single worksheet formula, so it may be unnecessary for a short list. In Google Sheets, the function is named SPLIT; its arguments and behavior are not identical to Excel’s TEXTSPLIT.

Choosing the Right Approach

Before choosing a method, consider how the data will be used after it is split. A quick formula is usually convenient for a live worksheet, while Text to Columns is practical for a one-off conversion and Power Query is designed for repeatable data preparation. The important question is whether the results need to refresh from the original text or simply become separate cell values.

  • Choose TEXTSPLIT when the source may change and you want the separated output to recalculate.
  • Choose Text to Columns when you want to split a selected range once and keep the resulting values.
  • Consider Power Query when splitting is one step in a recurring import or a wider cleanup process.
  • Check the delimiter and empty-field rules before splitting, especially when the order of the output columns matters.

In Excel for Mac, TEXTSPLIT is available in supported Microsoft 365 and Excel 2024 versions, although ribbon layout and some interface details can differ from Windows. The formula’s basic syntax and spill behavior are the same. Excel for the web also supports dynamic-array results, but the available interface for managing data may differ from the desktop app.

Frequently Asked Questions

What is the Excel formula to split text?
Use TEXTSPLIT. For text in A2 separated by commas, and with A2 as the source cell, enter =TEXTSPLIT(A2,","). Excel returns the pieces as a spilling array across columns.

How do I split text into rows instead of columns?
Supply the row delimiter as the third argument and leave the column-delimiter argument empty. For pipe-separated text in A2, and A2 as the source cell, use =TEXTSPLIT(A2,,"|"). The results spill downward in one column.

Why does TEXTSPLIT show #SPILL?
Excel cannot place one or more results in the required spill range. Remove or move blocking content, check for merged cells, and make sure the formula is not inside a table where the array would need to spill.

Can TEXTSPLIT use multiple separators?
Yes. Supply an array of delimiters, such as a comma and semicolon, so either character can mark a split. Check that none of the chosen characters also appears inside values that should remain intact.

Does TEXTSPLIT remove spaces automatically?
No. Spaces beside a delimiter remain part of the split value. If you want to remove extra leading and trailing spaces, wrap TEXTSPLIT in TRIM, while remembering that TRIM also collapses repeated internal ordinary spaces.

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.