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
TEXTSPLITin 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, andFIND.
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 isRiley. - To return the text after the first comma, enter
=MID(A2,FIND(",",A2)+1,LEN(A2)). The result isChen.
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 supportTEXTSPLIT, 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
TRIMto 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
- Excel Remove Duplicates: Is Matching Case-Sensitive?
- Excel Remove Duplicates From Multiple Columns: Steps
- Excel Remove Duplicates Based on One Column, Keep Rows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Drag a Number Sequence in Excel Without GapsTo drag a number sequence in Excel, enter the first two numbers in adjacent cells, select both cells,…
- SUMIF in Excel: Examples and How to Use ItExcel’s SUMIF function adds values that meet one condition. For example, with product names in A2:A7 and sales…
- Excel Let Cell Overflow? How to Show Text Across CellsYes. In Excel for Microsoft 365 on Windows, text can overflow into the cells to its right when…
- Dependent Dropdown in Excel Using INDIRECT: Setup GuideYes. Create a named range for each parent choice, then use INDIRECT in the child dropdown’s Data Validation…
- Excel Slicer Custom Sort: Control the Order of ItemsTo custom-sort an Excel slicer, create or use an Excel custom list for the item labels, then open…
- Nested IF Statements in Excel: Examples, Limits, and TipsNested IF statements in Excel put one IF function inside another, letting a formula choose among several outcomes.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.