Excel Formula to Split Text by Delimiter: How It Works

Formulas & functions · Updated November 18, 2026

In Microsoft 365 for Windows, split text with =TEXTSPLIT(A2,","), replacing A2 with the cell that contains your text and the comma with your delimiter. The formula returns each piece in its own cell, spilling across the row; use a row delimiter argument to spill down a column instead.

What “split by delimiter” means

A delimiter is the character or sequence of characters that marks where one piece of text ends and the next begins. A comma, pipe, slash, space, tab, or semicolon can all serve as delimiters. For example, splitting north|east|west on the pipe produces three values: north, east, and west.

Splitting is useful when a cell contains several values that need to be sorted, counted, filtered, or used in other formulas. Common examples include separating first and last names, dividing a city and state, extracting tags from a list, or breaking an imported code into parts. The delimiter must match the actual character in the cell: a hyphen is not an en dash, and a regular space is not the same as a nonbreaking space.

Use TEXTSPLIT for a horizontal split

TEXTSPLIT is the most direct formula option in current Excel. It is available in Microsoft 365 and Excel 2024. In older versions that do not include TEXTSPLIT, use Text to Columns or a legacy formula approach described below.

With the source text in A2 as north|east|west, enter this formula in B2:

=TEXTSPLIT(A2,"|")

The result is north in B2, east in C2, and west in D2. Only the first cell needs a formula. Excel places the other results in adjacent cells automatically; this is called spilling. Leave those cells empty so the result can expand.

The general syntax is TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with]). The first argument is the text or cell reference to split. The second is the delimiter that separates results across columns. The remaining arguments are optional and control splitting into rows, empty pieces, matching, and uneven results.

Split results down a column

To return each piece on a separate row rather than across columns, supply a row delimiter as the third argument and leave the column delimiter blank. With A2 containing north|east|west, enter this in B2:

=TEXTSPLIT(A2,,"|")

Excel returns north, east, and west down B2:B4. The two commas next to each other are intentional: they skip the second argument, which is for a column delimiter, and provide the pipe as the third argument, the row delimiter. This is handy when a source cell contains a list that should become a vertical range.

You can also provide both delimiters when the text has separate row and column boundaries. For instance, a text value organized with commas within records and line breaks between records can be split into a two-dimensional range. For line breaks, Excel’s CHAR(10) represents a line-feed character, so a row delimiter can be specified with that function when the source uses line feeds.

Handle repeated delimiters and extra spaces

By default, TEXTSPLIT preserves empty pieces created by consecutive delimiters. If A2 contains north||west, splitting on the pipe leaves an empty result between north and west. To ignore empty pieces, set the fourth argument to TRUE. With that same source in A2, use:

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

The result contains north and west with no empty item between them. This setting also affects leading or trailing delimiters, which can otherwise create empty results at the beginning or end. Keep the default when an empty field has meaning in your data; ignore empties only when those gaps are accidental or irrelevant.

Splitting does not automatically remove spaces around each result. If A2 contains north, east, west, a comma delimiter leaves a leading space before east and west. One option is to trim the split results with the dynamic-array function TRIM. With that text in A2, enter:

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

TRIM removes ordinary leading and trailing spaces from each returned item. It does not remove every unusual whitespace character, such as a nonbreaking space imported from a web page. If trimming appears not to work, inspect the source for a different character rather than assuming the delimiter formula is wrong.

Split on more than one delimiter

TEXTSPLIT can accept an array of delimiters, which is useful when the source uses inconsistent punctuation. With A2 containing north,east;west, this formula treats either a comma or semicolon as a separator:

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

The array constant lists both possible delimiters. A comma between items inside an array constant is standard in many English-language Excel installations, but formula separators can vary with regional settings. If Excel rejects a formula copied from another region, check the list and argument separators required by your installation.

When delimiter matching matters, TEXTSPLIT has a match_mode argument: 0 requests a case-sensitive match and 1 requests a case-insensitive match. Case is relevant when the delimiter is text rather than punctuation. For example, if the delimiter is the word “and,” case-insensitive matching can also recognize “And.” Punctuation delimiters usually do not create a case distinction.

What happens to the results

TEXTSPLIT returns a dynamic array. The formula lives in the upper-left cell of its output range, and Excel displays the rest of the results in the cells to the right or below. Click the formula cell to see the outline around its spill range. To refer to the full result elsewhere, use the spill operator after the formula cell reference; for example, if the formula is in B2, B2# refers to its complete spilled range.

If cells in the required output area contain data, Excel cannot place the full result and may show #SPILL!. Clear the obstructing cells or move the formula to an area with enough room. A spilled formula also cannot expand normally from inside an Excel Table. Put the formula outside the table, or use another method if the result must be part of a table layout.

  • Check that the source cell contains the delimiter you specified.
  • Make sure the cells where results should appear are empty.
  • Confirm that the output will not run into a worksheet edge or an Excel Table.
  • Check for spaces, line breaks, or visually similar characters in the source.

Alternatives for older Excel versions

If TEXTSPLIT is unavailable, the Data tab’s Text to Columns command can divide a selected column without a modern dynamic-array formula. Select the source cells, choose Data > Text to Columns, select Delimited, choose the delimiter, and complete the wizard. Text to Columns writes results into worksheet cells, so select a destination with enough empty columns; existing content can be overwritten. This method is often convenient for a one-time cleanup, but it does not automatically update the split values as the source text changes.

For a formula-based split in older Excel, the following examples assume A2 contains red,green,blue and each item is separated by a comma. In B2, this formula returns the text before the first comma:

=LEFT(A2,FIND(",",A2)-1)

It returns red. In C2, this formula returns the text between the first and second commas:

=MID(A2,FIND(",",A2)+1,FIND(",",A2,FIND(",",A2)+1)-FIND(",",A2)-1)

It returns green. In D2, this formula returns the text after the second comma:

=RIGHT(A2,LEN(A2)-FIND(",",A2,FIND(",",A2)+1))

It returns blue. These examples assume exactly three items and two commas. They are less flexible than TEXTSPLIT: if the number of items varies, the formula positions need to be adapted, and missing delimiters can cause FIND to return an error. For recurring imports, Power Query may be a better repeatable option: use Data > From Table/Range, select the text column, then choose Split Column and specify the delimiter. Menu wording can vary slightly between Excel builds.

Choose the right method

For a formula that should recalculate whenever the source changes, TEXTSPLIT is usually the clearest choice in Microsoft 365 or Excel 2024. It handles multiple pieces in one formula and can return results horizontally or vertically. For a one-time conversion in an older version, Text to Columns is straightforward. For repeatable imports and cleanup steps, Power Query can record a transformation that can be refreshed.

Consider these practical factors before choosing:

  • Excel version: TEXTSPLIT requires Microsoft 365 or Excel 2024; it is not available in every perpetual or older release.
  • Changing source data: A formula recalculates; Text to Columns makes a static output at the time it is run.
  • Variable item counts: TEXTSPLIT can spill the number of pieces it finds, while fixed-position legacy formulas usually need more planning.
  • Repeatable imports: Power Query is worth considering when the same split and cleanup must be repeated.
  • Output space: Any method that writes several results needs a suitable destination range.

Frequently Asked Questions

Can Excel split text into separate cells automatically?
Yes. In Microsoft 365 or Excel 2024, enter TEXTSPLIT in the first destination cell. Excel spills the returned pieces into neighboring cells, provided the output area is clear.

How do I split text at a space?
Use a regular space as the delimiter. With the text in A2, enter =TEXTSPLIT(A2," "). If there are repeated spaces or spaces around the resulting words, decide whether to ignore empty results or trim the output.

Can TEXTSPLIT return results vertically?
Yes. Leave the column-delimiter argument blank and provide the separator as the row-delimiter argument, as in =TEXTSPLIT(A2,,","). The results spill down the worksheet.

Why do I see #SPILL! after entering the formula?
One or more cells in the required output range are not available, or the formula is in a location where the array cannot spill, such as inside an Excel Table. Clear the obstructing cells or move the formula outside the table.

Does TEXTSPLIT work in Excel 2019 or Excel 2021?
TEXTSPLIT is available in Microsoft 365 and Excel 2024, not Excel 2021 or Excel 2019. In those older versions, use Text to Columns, Power Query, or formulas built from functions such as LEFT, MID, FIND, and RIGHT.

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.