Excel Split Cell Contents by Delimiter

How-to basics · Updated November 21, 2026

To split cell contents by a delimiter in Excel for Microsoft 365 on Windows, select the cells and choose Data > Text to Columns, then choose Delimited and specify the separator. For a formula-based split that updates when the original text changes, use TEXTSPLIT; for repeatable cleanup of imported data, use Power Query.

What splitting by a delimiter means

A delimiter is a character or text string that marks the boundary between parts of a value. Common examples include commas in a list, tabs between fields, pipes in exported data, and spaces between a first and last name. If cell A2 contains Chen, Maya, 1048, splitting on commas places the three pieces into neighboring cells.

Splitting is different from simply displaying text on multiple lines. It changes where the pieces are stored: they may occupy separate columns or separate rows. It also differs from fixed-width splitting, where Excel divides text at positions you choose rather than at a specific character. The right method depends on whether you need a quick one-time operation, a live formula result, or a repeatable import process.

Use Text to Columns for a one-time split

Text to Columns is a practical choice when the source values are already in a worksheet and you want to convert them into separate cells. The Windows steps are:

  1. Select the cells containing the text. If you select a single cell within a contiguous list, Excel may detect the surrounding range, but selecting the full source range makes the intended operation clear.
  2. Choose Data > Text to Columns.
  3. In the wizard, choose Delimited and select Next.
  4. Check the delimiter you need, such as Tab, Comma, Space, or Other. For a custom character, type it in the Other box. The preview shows how Excel will divide the sample text.
  5. Choose Next if you need to set a column data format or a text qualifier. Otherwise, choose Finish.

For example, if A2:A4 contains red|small|12, blue|large|7, and green|medium|15, choose Other and enter the pipe character. Excel puts each field into its own column. If you do not want the result to replace or collide with existing content, set a different destination in the wizard before finishing.

Text to Columns changes the selected data rather than creating a formula linked to it. If you edit the original text later, the split cells do not update automatically. Keep a copy of the source column if you may need the original values.

Split with the TEXTSPLIT formula

In Microsoft 365 or Excel 2024, TEXTSPLIT can return the pieces of a text value into adjacent cells. It is useful when you want the split to recalculate whenever the source changes. With the delimited text in A2 and a comma as the separator, enter this formula in an empty cell:

Assuming A2 contains Chen, Maya, 1048, enter =TEXTSPLIT(A2,","). The result spills across three cells as Chen, Maya, and 1048; the spaces after commas remain because they are not part of the delimiter.

To remove spaces around the parts, you can split on the comma and clean the resulting cells with other functions, or use a delimiter that includes the comma and following space when the source consistently uses that exact pattern. For example, if A2 contains Chen, Maya, 1048 with comma-space separators, enter =TEXTSPLIT(A2,", "). It returns Chen, Maya, and 1048.

TEXTSPLIT can also divide a value vertically. With the text in A2 and a comma between items, enter =TEXTSPLIT(A2,,","). The omitted second argument leaves the column delimiter blank; the third argument specifies a row delimiter, so the results spill down instead of across. Make sure the cells where results need to appear are empty.

TEXTSPLIT is available in Microsoft 365 and Excel 2024, but not in every older Excel release. If Excel shows #NAME?, the installed version may not include the function. Text to Columns or Power Query may be better options for those workbooks.

Choose between columns and rows

Most people expect one item per column, but a vertical list may suit a different task. For example, splitting a comma-separated set of tags across a row makes it easy to compare fields in a table; placing one item per row is often more convenient for filtering, counting, or appending records to a list.

Text to Columns creates columns. TEXTSPLIT can create columns or rows by using its column-delimiter and row-delimiter arguments. When splitting down rows, consider where existing records are located: a spilled formula cannot overwrite occupied cells. If each source record needs a variable number of rows, plan the output area so results do not overlap one another.

Use Power Query for recurring imports

Power Query is useful when you repeatedly receive files that need the same cleanup. In Excel for Windows, select a cell in the source data and choose Data > From Table/Range. Confirm the table range if prompted. In the Power Query Editor, select the column, then choose Home > Split Column > By Delimiter. Choose the separator and whether to split at each occurrence or at a specified occurrence, then confirm the operation.

When the transformation is ready, use Home > Close & Load to return the results to Excel. A query can be refreshed when the source data changes, making this approach more repeatable than manually running Text to Columns each time. It is especially helpful for a folder or regular export workflow, though the setup takes longer than a quick worksheet split.

Power Query availability and feature details can vary across Excel editions. Excel for the web does not have the same full authoring experience as Excel for Windows. On Mac, available Power Query capabilities depend on the Excel version; if the commands are absent, use Text to Columns or a formula instead.

How to choose the right method

  • Text to Columns: Choose it for a fast, one-time split of a worksheet column. It is straightforward and works without a newer dynamic-array function.
  • TEXTSPLIT: Choose it when you want a formula result that follows changes to the source, or when you need the results to spill into rows or columns.
  • Power Query: Choose it when you repeat the same import and cleanup steps and want to refresh the transformed output.

Think about what should happen when the source changes. A manual split is static, while a formula recalculates and a query can be refreshed. Also consider whether neighboring cells are available, whether the delimiter is consistent, and whether Excel must preserve the values as text. These details can matter more than the method’s speed.

Common delimiter problems and fixes

  • Extra spaces: A comma delimiter does not remove spaces around each item. Use a comma-space delimiter if that pattern is consistent, or clean the split results separately.
  • Empty fields: In one,,three, the two adjacent commas indicate an empty middle field. TEXTSPLIT can preserve or ignore empty results using its ignore_empty argument. With this text in A2, enter =TEXTSPLIT(A2,",",,TRUE) to ignore empty fields; the output is one and three.
  • Delimiter appears inside a value: Splitting every comma in Paris, France, 75000 creates three fields even if the intended first field is Paris, France. Check the source format and use a delimiter or import method that respects its quoting rules, rather than assuming every occurrence marks a boundary.
  • Existing cells get replaced: Text to Columns writes results into adjacent columns. Check those cells first or choose a different destination in the wizard.
  • Numbers lose leading zeros: An identifier such as 00421 may be interpreted as a number and become 421. In the Text to Columns wizard, select the affected output column and set its format to Text before finishing.
  • Formula shows #SPILL!: A TEXTSPLIT result needs an unobstructed output area. Clear cells in the spill range, including cells that look blank but contain spaces or formulas.

For imported CSV data, do not assume that every comma should be treated as a boundary. Some formats put quotes around fields that themselves contain commas. Importing with the appropriate text qualifier or using a query that understands the file structure can prevent those fields from being split incorrectly.

Frequently Asked Questions

Can Excel split a cell by a space?
Yes. In Text to Columns, select Space as the delimiter. In Microsoft 365 or Excel 2024, TEXTSPLIT can also use a space as its delimiter. If names contain multiple spaces, review how empty pieces should be handled.

Can I split one cell into rows instead of columns?
Yes. Use TEXTSPLIT with its row-delimiter argument, such as =TEXTSPLIT(A2,,",") for comma-separated items in A2. Text to Columns is designed to produce columns, so it is not the direct choice for a vertical result.

Does Text to Columns keep the original text?
It replaces the selected source values with the first split field and places other fields in columns to the right. Copy the source column first or choose another destination if you need to retain the unsplit text.

Why is TEXTSPLIT not working in my Excel?
TEXTSPLIT requires Microsoft 365 or Excel 2024. An older version may show #NAME? because it does not recognize the function. Use Text to Columns or Power Query if available.

Can Excel split on more than one delimiter?
Yes, but the best method depends on the source and Excel version. Text to Columns allows multiple delimiter options, while TEXTSPLIT accepts delimiter arguments. Preview or test on a copy first, especially when separators also occur inside field values.

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.