Excel Formula to Split Text in a Cell: A Practical Guide

Formulas & functions · Updated November 19, 2026

In Excel for Microsoft 365, split text in a cell with =TEXTSPLIT(A2,","), replacing the comma with the character that separates your text. The result spills into neighboring cells; if you use an older Excel version, use Text to Columns or a combination of text formulas instead.

What splitting text means in Excel

Splitting text means taking one cell’s contents and separating it into two or more parts based on a delimiter: a character or sequence that marks the boundary. For example, a source cell containing Chen, Mira can become one cell with Chen and another with Mira. A product code such as AB-204-XL could be divided at each hyphen.

First decide what counts as a separator. It might be a comma, space, hyphen, slash, line break, or a fixed number of characters. A formula can update automatically when the source text changes, which is useful for recurring imports and reports. For a one-time cleanup, Excel’s Text to Columns command may be faster.

Split text with TEXTSPLIT

TEXTSPLIT is the most direct formula for separating text at a known delimiter. It is available in Microsoft 365 and Excel 2024. In the example below, A2 contains Chen, Mira, Seattle; the formula separates the text at each comma and returns Chen, Mira, and Seattle in consecutive columns on the same row.

With the text to split in A2 and commas between each part, enter:

=TEXTSPLIT(A2,",")

The function returns a dynamic array: Excel places the first item in the formula cell and spills the remaining items into adjacent cells. You do not need to copy the formula across. Leave enough blank space to the right of the formula cell, or Excel may show a #SPILL! error. If A2 contains Chen, Mira, Seattle, the three results are Chen, Mira, and Seattle.

To put separated parts down a column rather than across a row, supply the separator as the row delimiter. With A2 containing red|blue|green, this formula returns the three colors in separate rows:

=TEXTSPLIT(A2,,"|")

The empty argument leaves the column delimiter unspecified, while the pipe character is used as the row delimiter. This is useful when a cell contains a list that should become a vertical set of records.

Handle spaces and empty parts

Data entered by people often includes a space after a comma. If A2 contains Chen, Mira, Seattle, splitting at the comma leaves a leading space before Mira and Seattle. That space may be hard to notice, but it can affect matching, sorting, or comparisons. Wrap the split in TRIM to remove extra spaces around the returned parts.

With comma-separated text in A2 and ordinary spaces around the values, use:

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

TRIM removes leading and trailing spaces and reduces repeated internal spaces to one, so consider whether that change is appropriate for your data. It does not remove every kind of whitespace, such as all nonbreaking spaces copied from a web page.

Consecutive delimiters create empty parts. For example, red||green has an empty item between the two pipe characters. If empty items should be skipped, use the optional ignore_empty argument. With the pipe-separated text in A2, this formula ignores empty parts:

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

Here, TRUE is the fourth argument, which tells Excel to ignore empty results. Without it, retaining the empty position may be important because it reflects the structure of the source data. For instance, an empty field in a data export might mean “unknown,” not “remove this field.”

Extract only the text before or after a delimiter

You do not always need to split every part into separate cells. If you only need the first part or the remainder after a separator, TEXTBEFORE and TEXTAFTER can be clearer. These functions are available in Microsoft 365 and Excel 2024.

With A2 containing Chen, Mira, use this formula to return the text before the first comma:

=TEXTBEFORE(A2,",")

With the same value in A2, use this formula to return the text after the first comma:

=TEXTAFTER(A2,",")

The second result includes the space after the comma, so you can remove it by wrapping the function in TRIM. These functions are handy when a workbook needs a single field, such as a surname, rather than a complete set of split columns. They can also make a formula easier to read than older combinations of LEFT, MID, and FIND.

Older Excel formulas for a two-part split

If your Excel version does not have TEXTSPLIT, you can split text around a delimiter with older text functions. The following formulas divide a value at its first comma. They work well for two-part data such as Chen, Mira, but are less convenient when the text contains many parts.

With the full text in A2 and a comma separating the two parts, enter this formula to return the text before the comma:

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

For A2 containing Chen, Mira, the result is Chen. FIND locates the comma’s position; subtracting one tells LEFT to stop just before it.

With the same layout, use this formula to return and trim the text after the first comma:

=TRIM(MID(A2,FIND(",",A2)+1,LEN(A2)))

The result is Mira. MID starts one character after the comma and returns up to the length of the original value; TRIM removes the leading space. These formulas return an error if the delimiter is missing. If a missing comma should leave the original text intact, use this formula for the first part:

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

That fallback is a deliberate choice: it returns the complete A2 value when FIND cannot locate a comma. If the delimiter is expected in every row, it may be better to identify and correct malformed source data rather than silently treating it as a complete field.

Use Text to Columns for a one-time split

Text to Columns separates existing values into worksheet cells without requiring a formula. It is available in the desktop Excel application. On Windows, select the cells, then choose Data > Text to Columns. On Mac, select the cells and use Data > Text to Columns as well; labels or dialog details can differ slightly between platform releases.

  1. Select the source column or range. If the adjacent cells contain data, choose a different destination before completing the operation so you do not overwrite it.
  2. Choose Delimited when a character such as a comma, tab, or space marks the split. Choose Fixed width when values should be divided at specific character positions.
  3. For a delimited split, select the relevant delimiter in the wizard and review the preview. Set a destination if the output should go somewhere other than the source range.
  4. Check the preview and finish the wizard. Confirm that the results landed in the intended columns and that leading zeros or other important text formatting were preserved.

Text to Columns changes the selected data rather than creating a live formula result. If the original source value changes later, you must run the command again. For a repeatable process, a formula or a data-import workflow may be a better fit. In Excel for the web, use a supported formula such as TEXTSPLIT when available; the desktop wizard is not a dependable option across web interfaces and accounts.

Choose the right method for your task

The best option depends on your Excel version, how often the source changes, and whether you need a permanent transformation or a live result. Use these practical guidelines:

  • Use TEXTSPLIT for a dynamic result with multiple parts when you have Microsoft 365 or Excel 2024.
  • Use TEXTBEFORE or TEXTAFTER when you need just the portion on one side of a delimiter and your version supports those functions.
  • Use LEFT, MID, FIND, and related older functions when compatibility with earlier Excel versions matters and the split pattern is simple.
  • Use Text to Columns when you want to split a fixed set of existing cells once, without maintaining formulas.
  • Use Power Query for repeatable imports and data-cleaning steps that need to be refreshed. In desktop Excel, Power Query tools are available from the Data tab, though the exact commands can vary with the source and platform.

Keep the original column until you have checked the results. This gives you a straightforward way to compare the split fields with the source and recover if a delimiter occurs in an unexpected place.

Common problems and how to fix them

A split formula can be correct yet produce an unwanted result if the input is inconsistent. Check these common causes before changing the formula:

  • Excel shows #NAME? Your version may not recognize a newer function such as TEXTSPLIT. Use a supported Excel version, Text to Columns, or an older formula approach.
  • Excel shows #SPILL! One or more cells where the dynamic result needs to go are occupied, merged, or otherwise unavailable. Clear the output area or move the formula. Dynamic-array results also cannot spill inside an Excel Table; place the formula outside the table or use another approach.
  • Some rows do not split The delimiter may differ between rows, such as a comma in some cells and a semicolon in others. Standardize the source or choose a formula designed for the actual separator.
  • Unexpected spaces remain Apply TRIM when ordinary spaces surround values. If data came from a website, a nonbreaking space may need separate cleanup because it is not treated like a regular space.
  • Text goes into the wrong fields A delimiter may also appear inside a value, such as a comma in an address. Confirm the source format and review a few representative rows before splitting a large range.
  • Leading zeros disappear Excel may interpret a split result as a number. If a code such as 0042 must remain text, check the destination formatting and import options; do not assume a displayed numeric value retains its original zeros.

Formula argument separators depend on regional settings. The examples here use commas between function arguments, as in the standard English-language Excel setup specified for this guide. If Excel rejects a formula, your regional configuration may require semicolons as argument separators; that is separate from the comma inside a quoted delimiter.

Frequently Asked Questions

What is the formula to split text by a comma in Excel?
With the comma-separated value in A2, use =TEXTSPLIT(A2,",") in Microsoft 365 or Excel 2024. The results spill into neighboring columns.

Can I split one cell into rows instead of columns?
Yes. With a pipe-separated list in A2, use =TEXTSPLIT(A2,,"|"). The parts spill into separate rows beneath the formula cell.

Does TEXTSPLIT work in every version of Excel?
No. It is available in Microsoft 365 and Excel 2024, not every older perpetual version. If the function is unavailable, use Text to Columns or compatible formulas such as LEFT, MID, and FIND.

How do I split a cell at the first space?
In Microsoft 365 or Excel 2024, replace the comma delimiter in a TEXTSPLIT formula with a quoted space. Check the source first: if names or labels can contain multiple spaces, splitting at every space may create more fields than intended.

Will a split formula update when I edit the original cell?
Yes. A formula that refers to A2 recalculates when A2 changes, and a dynamic-array result adjusts to the new text. Text to Columns, by contrast, writes the separated values and does not keep a live link to the original text.

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.