Split String Into an Array in Excel

How-to basics · Updated November 22, 2026

In Excel for Microsoft 365 on Windows, use TEXTSPLIT to split text into an array: if A2 contains red,blue,green, enter =TEXTSPLIT(A2,",") in an empty cell. Excel places the three results in neighboring cells as a dynamic array.

What “split a string into an array” means

A string is a piece of text, such as a list of names stored in one cell. Splitting it means dividing that text wherever a chosen separator, or delimiter, occurs. In the example above, the comma is the delimiter, and the resulting values are red, blue, and green.

In Excel, the result is usually a dynamic array: one formula returns several values, which spill into the cells beside or below the formula cell. You do not need to copy a formula across each output cell. The values are still text unless you convert them; splitting does not automatically make numbers, dates, or other entries into a different data type.

TEXTSPLIT is available in Microsoft 365 and Excel 2024. It is not included in some older perpetual versions of Excel. If Excel reports that the function is unknown, check your version or use the alternatives described below.

TEXTSPLIT syntax and the basic steps

The function’s syntax is TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). The first argument is the text or cell to divide. The second tells Excel what separates values placed in columns; the optional third argument identifies a separator for values placed in rows.

For a simple comma-separated list, assume A2 contains red,blue,green. Enter =TEXTSPLIT(A2,",") in an empty cell, such as B2. The result spills across B2:D2. The comma is removed from the output because it marks the boundary between values.

  1. Put the original text in a cell, for example A2.
  2. Choose an empty cell with enough open space to the right or below for the results.
  3. Enter TEXTSPLIT with the source cell and the delimiter.
  4. Press Enter. Excel displays the results in the spill range.

The formula belongs in the top-left cell of the output. If the source text changes, the displayed array updates automatically. You can refer to the whole result with the spill operator: for example, a formula that needs the output beginning in B2 can refer to B2#. This is useful when another calculation should use every split item, even if the number of items changes.

Choose the right delimiter

A delimiter can be a comma, space, slash, tab, or a longer text sequence. It must match the characters actually present in the source. If A2 contains North | South | West, with spaces on both sides of each vertical bar, splitting on just the bar leaves spaces at the edges of the values. You can instead split on the complete separator.

Assume A2 contains North | South | West. This formula uses the space-bar-space sequence as the delimiter and returns clean values across columns:

=TEXTSPLIT(A2," | ")

If a delimiter is stored in a cell, use that cell as the argument. For example, with the source text in A2 and a comma in B1, =TEXTSPLIT(A2,B1) splits using the value in B1. This can make a worksheet easier to maintain when users may choose a separator.

Spaces need a little thought. Splitting one two three on a single space works, but two spaces in a row create an empty item between them. If the text has inconsistent spacing, the next section’s ignore_empty option can help. If the values themselves contain the delimiter, a basic split cannot tell whether that character is part of a value or a separator. In that case, the source needs a consistent quoting or escaping convention, or a more structured import method.

Split into rows or columns

By default, TEXTSPLIT uses its column delimiter to return items horizontally. To put the items down a column, supply a row delimiter as the third argument and leave the column delimiter blank. For instance, if A2 contains text with line breaks between entries, the formula below returns one entry per row.

Assume A2 contains three lines, with each line separated by a line break entered in the cell:

=TEXTSPLIT(A2,,CHAR(10))

The two commas are intentional: the second argument is left blank, and CHAR(10) identifies the line-feed character as the row delimiter. A line break in a cell is commonly created with Alt+Enter on Windows; on Mac, the key combination can vary by Excel version and keyboard, so use the in-cell line-break shortcut shown for that version. The formula uses the same line-feed character in either case.

You can provide both a column delimiter and a row delimiter when the text is arranged as a simple grid. For example, a string with commas between fields and line breaks between records can be split into rows and columns. If different rows have different numbers of fields, the optional pad_with argument controls what fills the shorter rows; by default, Excel uses #N/A for missing positions.

Handle repeated delimiters and empty values

Two delimiters next to each other represent an empty field in many data formats. For example, pear,,plum has an empty value between the commas. By default, TEXTSPLIT preserves that blank position, which matters when the position represents missing information in a structured record.

If repeated separators are accidental and you want to skip the empty entries, set ignore_empty to TRUE. With A2 containing pear,,plum, this formula returns only pear and plum:

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

That setting also skips empty fields created by a delimiter at the beginning or end of the text. Do not use it automatically if empty positions carry meaning. For example, a blank middle field in an exported record might indicate that a person has no middle name or that a value was not supplied; removing it can shift later values into the wrong positions.

When separators are followed by unwanted spaces, you can trim each resulting item. Assume A2 contains pear, plum, peach:

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

TRIM removes ordinary leading and trailing spaces from the split results, including the spaces after commas in this example. It does not remove every kind of whitespace character; copied text may contain nonbreaking spaces that need separate cleanup.

Use the results in other formulas

Because the output is an array, it can feed other dynamic-array formulas or calculations. For instance, you can count the number of comma-separated items rather than counting the cells yourself. Assume A2 contains red,blue,green:

=COUNTA(TEXTSPLIT(A2,","))

The result is 3. If blank fields should not count, use the same ignore_empty setting in the split. You can also use the spill reference for follow-on formulas: if =TEXTSPLIT(A2,",") is in B2, then B2# refers to all cells currently produced by that formula. The spill reference adjusts as the array changes size.

TEXTSPLIT returns text values. If you split a list of numeric-looking entries and need to calculate with them, convert the results as appropriate rather than assuming they are already numbers. Also remember that splitting is a transformation of the displayed result, not a change to the original source cell. The original text remains intact unless you deliberately replace it.

Common errors and how to fix them

  • #SPILL!: One or more cells in the required output area are occupied, merged, or otherwise blocking the result. Clear the obstructing cells or move the formula to an area with enough room. Excel indicates the blocked spill range when you select the error.
  • #NAME?: Excel may not recognize TEXTSPLIT because the installed version does not support it, or because the function name was mistyped. Confirm that you have Microsoft 365 or Excel 2024 and check the spelling.
  • Extra blank results: The source may contain repeated delimiters. Decide whether those empty fields are meaningful; if they are not, set ignore_empty to TRUE.
  • Unexpected spaces: Spaces are characters, so they remain unless included in the delimiter or removed with a cleanup step such as TRIM. Check the actual separator in the source rather than assuming it is a comma followed by a space.
  • Output will not spill in a table: Dynamic array formulas do not spill inside an Excel table. Place the formula in the worksheet grid outside the table, leaving room for the result.

A common misconception is that pressing Enter after selecting several cells is required to create an array. For TEXTSPLIT in current Excel, enter the formula in its starting cell and press Enter; Excel manages the spill range. Do not type into cells within that range unless you intend to block or replace part of the output.

Alternatives when TEXTSPLIT is unavailable

If you only need to split a value once, Excel’s Text to Columns feature is a practical option. Select the source cells, then choose Data > Text to Columns on Excel for Microsoft 365 for Windows. Follow the wizard to choose a delimited format and select the separator. This changes the selected cells into separate columns rather than creating a formula-driven result that updates from the source. Make a copy first if the original data must be preserved.

For repeated imports or larger datasets, Power Query can split a column by a delimiter and refresh the transformation when the source is updated. In Excel for Microsoft 365 on Windows, select the data and choose Data > From Table/Range, then use the split-column options in the Power Query editor. Menu labels and available options can differ somewhat on Mac and Excel for the web. Unlike TEXTSPLIT, Text to Columns and Power Query are workflow tools, not a single worksheet formula returning a spilling array.

Frequently Asked Questions

How do I split a string by a comma in Excel?
With the text in A2, enter =TEXTSPLIT(A2,",") in an empty cell. The comma-separated items spill horizontally. Ensure the cells to the right are clear.

How do I split text into rows instead of columns?
Use the third argument for the row delimiter and leave the column delimiter blank. For line breaks in A2, enter =TEXTSPLIT(A2,,CHAR(10)).

Can TEXTSPLIT use more than one delimiter?
It accepts a delimiter argument, which can be a value such as a comma or a multi-character sequence. If your text uses several different separator types, standardize the text first or use a deliberate cleanup or import workflow so separators are handled consistently.

Why does TEXTSPLIT return #SPILL!?
The formula’s output area is blocked, often by existing content or merged cells. Clear the cells where the results need to appear, or move the formula to a larger empty area.

Does TEXTSPLIT work in older Excel versions?
It requires Microsoft 365 or Excel 2024. If your version does not include it, use Text to Columns for a one-time split, or Power Query for a repeatable import and transformation.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.