Excel Split Cell String by Delimiter: Formulas and Tools

Formulas & functions · Updated November 21, 2026

In Excel for Microsoft 365, split a cell’s text at a delimiter with TEXTSPLIT: if A2 contains red,blue,green, enter =TEXTSPLIT(A2,",") in an empty cell to spill the three values into neighboring columns. For a one-time split, use Data > Text to Columns; for repeatable imports, consider Power Query.

Quick answer: choose a splitting method

The right method depends on whether the split should update when the source changes, whether you have a current Excel version, and whether you are transforming a few cells or a recurring dataset. A formula is usually best when the result should stay linked to the source. Text to Columns is direct and convenient for a one-off cleanup. Power Query is designed for repeatable data preparation.

  • Dynamic formula: Use TEXTSPLIT in Microsoft 365 or Excel 2024. It separates text into a spill range that recalculates when the source changes.
  • One-time worksheet edit: Use Text to Columns to split selected cells into adjacent columns.
  • Repeatable import: Use Power Query when you will need to apply the same split to refreshed data.
  • Older Excel: Use Text to Columns or formulas built from functions such as LEFT, MID, and FIND.

A delimiter is the character or string that marks a boundary: a comma, space, hyphen, slash, tab, or another known separator. Splitting does not infer what the parts mean. A comma-separated cell could contain first and last names, product codes, or unrelated values; Excel simply separates at the delimiter you specify.

How the TEXTSPLIT function works

TEXTSPLIT is available in Microsoft 365 and Excel 2024, not Excel 2021 or earlier perpetual versions. Its basic form takes the text to divide and a column delimiter. With the text north|east|west in A2, this formula produces three cells across the row:

With the text to split in A2, enter: =TEXTSPLIT(A2,"|"). The result is north, east, and west in three adjacent columns.

The function returns a dynamic array. You enter it in one cell, and Excel places the result in the necessary neighboring cells. Those cells are called the spill range; do not type into them while the formula is in place. If a cell in the required range is occupied, Excel displays a #SPILL! error. Clear the obstructing cells or move the formula.

Because the formula refers to A2, changing A2 updates the split result. You can copy the formula down for other records, provided each row has a compatible output area. If the destination needs to be permanent values rather than live formulas, copy the results and use Paste Special > Values.

Split into columns or rows

By default, the second argument to TEXTSPLIT is a column delimiter, so parts appear horizontally. To make the pieces spill vertically instead, supply a row delimiter as the third argument and leave the column-delimiter argument empty.

With red,blue,green in A2, enter this formula to put each color on a separate row: =TEXTSPLIT(A2,,","). The blank argument between the commas tells Excel that no column delimiter is being supplied. The comma in quotation marks is the row delimiter.

Orientation matters when you are preparing a table. Splitting a list across columns can create one record per row, while splitting down rows can make a long list easier to filter or append. Check the cells around the formula first: a horizontal result needs clear cells to the right, and a vertical result needs clear cells below.

Multiple delimiters, blanks, and extra spaces

A string may use more than one separator—for example, commas between some values and semicolons between others. In Microsoft 365 or Excel 2024, a delimiter array can tell TEXTSPLIT to recognize both. With apple,pear;plum in A2, enter =TEXTSPLIT(A2,{",",";"}) to split at either character. In some regional settings, Excel uses different list separators in formulas and array constants, so the displayed punctuation may need to match the settings on your computer.

Separators may also leave spaces around the pieces. For example, splitting red, blue, green at the comma leaves a leading space before the second and third values. With that text in A2, use =TRIM(TEXTSPLIT(A2,",")) to remove leading and trailing spaces from each returned item. Note that TRIM also reduces repeated internal spaces to single spaces, which may not be appropriate if those spaces are meaningful.

Repeated delimiters create empty pieces unless you tell the function to ignore them. With one,,three in A2, =TEXTSPLIT(A2,",",,TRUE) ignores the empty segment between the two commas and returns one and three. If the empty position represents missing data that must be preserved, leave the option out rather than discarding it.

Use Text to Columns for a one-time split

Text to Columns is useful when you want to turn existing cells into separate values without keeping a formula connected to the original. In Excel for Microsoft 365 on Windows, select the cells, then choose Data > Text to Columns. In the wizard, choose Delimited, select Next, choose the delimiter, review the preview, and select Finish. The tool writes the pieces into the selected column and cells to its right.

Before finishing, make sure the cells to the right are empty or that you are comfortable replacing their contents. Text to Columns can overwrite existing data in its output area. The wizard also offers options such as treating consecutive delimiters as one and choosing a text qualifier. A text qualifier, commonly a quotation mark, can keep a delimiter inside a quoted field from being treated as a boundary.

For example, a comma-delimited row containing Riley,Chen,West can be split into three columns by choosing comma as the delimiter. If the source is a real CSV file with quoted fields, inspect the preview and qualifier settings carefully: a value such as "Chen, Riley" may be intended as one field, not two. The appropriate setting depends on how the source file is structured.

Text to Columns is a desktop Excel feature with a wizard-based workflow; the precise appearance can differ between Windows and Mac. On Mac, select the data and look for Data > Text to Columns. Check the preview before committing the split, and leave room for the output columns. Unlike a formula, this operation does not automatically redo the split when the original text changes.

Use Power Query for recurring data

Power Query is often the better option when the same kind of text arrives repeatedly, such as a monthly export. It records transformation steps so they can be applied again when you refresh the query. In Excel, select the source data and use Data > From Table/Range to open the Power Query Editor. Select the text column, then choose Transform > Split Column > By Delimiter. Choose the separator and whether to split at each occurrence or at a specified occurrence, then confirm.

When the result looks right, use Home > Close & Load to return the transformed data to Excel. Later, refresh the query after updating its source. Power Query is not a live formula in each worksheet cell: it reruns the recorded transformation when refreshed. That distinction is useful for controlled import workflows, but it means a refresh is needed to bring in changed source data.

Power Query can also split a column into rows, which is helpful when a cell holds a list of items that should become separate records. Consider the intended table shape before choosing the option: splitting a customer’s tags into columns preserves one customer per row, while splitting tags into rows creates multiple rows for that customer. The latter may require retaining an ID column so each tag can still be associated with its source record.

Options for older Excel versions

If your Excel version does not include TEXTSPLIT, Text to Columns is usually the simplest built-in choice. For a fixed layout with a single delimiter and exactly two parts, a formula can extract each side. These formulas assume A2 contains Riley,Chen and the comma is the first delimiter:

  • To return the text before the first comma, enter =LEFT(A2,FIND(",",A2)-1). The result is Riley.
  • To return the text after the first comma, enter =MID(A2,FIND(",",A2)+1,LEN(A2)). The result is Chen.

These formulas are intentionally limited: they split only at the first comma, and they return an error if no comma is present. If records have a varying number of pieces, several delimiters, or optional fields, building a long chain of older formulas becomes harder to maintain. Text to Columns or Power Query is generally clearer in those cases. Newer functions such as TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024, but do not replace the broader, multi-part split behavior of TEXTSPLIT.

Common problems and how to fix them

  • The formula shows as text: The cell may be formatted as Text, or the formula may begin with an apostrophe. Change the number format to General, then re-enter the formula.
  • You see #NAME?: Your Excel version may not support TEXTSPLIT, or the function name may be misspelled. Use Text to Columns or Power Query if the function is unavailable.
  • You see #SPILL!: Clear the cells where the returned array needs to appear. Merged cells in the spill area can also prevent the result from expanding.
  • Some pieces retain spaces: The delimiter may be only a comma while the source has a comma followed by a space. Apply TRIM to the result if removing surrounding spaces is appropriate.
  • Values split in the wrong place: Confirm that the delimiter is exactly the character in the data. A hyphen, en dash, nonbreaking space, and ordinary space are distinct characters.
  • Leading zeros disappear: A split value that looks numeric may be interpreted or formatted as a number. If identifiers such as postal codes or product codes must keep zeros, set the destination format to Text before using Text to Columns, or use an appropriate text-preserving workflow.

One frequent misconception is that splitting a cell is the same as separating every word. Excel does not know whether a space inside a name is a boundary, or whether a comma belongs inside quoted text. The delimiter and the source’s data rules determine the result. If the source is inconsistent, inspect and normalize it before relying on an automated split.

How to choose the right approach

For a few cells in a modern workbook, start with TEXTSPLIT because the result updates as the source changes and the formula is easy to audit. Use Text to Columns when the task is a one-time conversion and you want the split values to replace or sit beside the source. Use Power Query when the input is imported regularly or when the same transformation has several steps.

Also consider how the output will be used. A formula spill range is convenient for analysis but depends on having clear space around it. A static split is easier to hand off when no ongoing link is needed. A query is more manageable when the source is refreshed, but it introduces a refresh step and a query to maintain. Before choosing, test a few representative records, including the longest string, empty fields, and any values that contain the delimiter as ordinary text.

Frequently Asked Questions

Can Excel split text at every occurrence of a delimiter?
Yes. In Microsoft 365 or Excel 2024, TEXTSPLIT separates at each occurrence of the specified delimiter by default. Text to Columns can also split at each selected delimiter for a one-time operation.

Can I split a cell into rows instead of columns?
Yes. In TEXTSPLIT, supply the separator as the third, row-delimiter argument and leave the second argument blank. Power Query also offers a split-into-rows option.

Does TEXTSPLIT change the original cell?
No. It returns the split pieces in a separate spill range; the source text remains in its original cell. Text to Columns, by contrast, writes the split results into the selected area and neighboring cells.

Why does my split formula return a spill error?
Excel cannot place the full result because one or more required destination cells are occupied, merged, or otherwise unavailable. Clear the spill area or move the formula to a location with enough room.

Can I split on a space or a hyphen?
Yes. Put the exact separator inside quotation marks as the delimiter argument, such as a single space or hyphen. If the data contains different kinds of dashes or spaces, verify the actual characters before selecting the delimiter.

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.