To split a cell at a comma, space, or another delimiter in Microsoft 365 Excel, enter a TEXTSPLIT formula in an empty cell; for example, with the delimited text in A2, use =TEXTSPLIT(A2,","). To split existing values in place instead, select the cells and choose Data > Text to Columns.
Choose a formula or Text to Columns
The right method depends on whether the split should update when the source changes. A formula keeps the original value and displays the separated parts elsewhere. Text to Columns changes the selected cells, so it is useful for a one-time cleanup or when you want to replace the source data.
- Use TEXTSPLIT when you want a repeatable result that recalculates from the source, or when new rows will need the same treatment.
- Use Text to Columns when you want a guided, one-time split of a selected range without writing a formula.
- Use Power Query when you regularly import or refresh larger datasets and want the split to be part of a repeatable transformation.
TEXTSPLIT is available in Microsoft 365 and Excel 2024. It is not available in every older Excel release. If you do not recognize the function, check your Excel version or use Text to Columns or Power Query instead.
Split a value with TEXTSPLIT
TEXTSPLIT separates text wherever it finds the delimiter you specify. With a comma-delimited value in A2, enter this formula in B2:
Assumed layout: A2 contains Lee,Jordan,Sales, and B2 is empty.
=TEXTSPLIT(A2,",")
The result spills across three cells: B2 contains Lee, C2 contains Jordan, and D2 contains Sales. The original value in A2 remains unchanged. Make sure the cells to the right are empty, because a value already in the spill range can cause a #SPILL! error.
To apply the same split to multiple rows, copy the formula down beside the source values. Excel adjusts the row reference automatically, so the formula beside A3 refers to A3. If your data is in an Excel table, a formula entered in a table column can fill down as a calculated column, although spilled results need clear space outside the table.
Choose the delimiter and split direction
The delimiter is the character or text sequence that marks the boundary between parts. It can be a comma, semicolon, space, hyphen, slash, or another string. Match the delimiter to the actual data: a comma and a space are different delimiters, and a value containing comma-space may need that full sequence specified.
Assumed layout: A2 contains North, East, West, with the comma followed by one space; B2 and the cells to its right are empty.
=TEXTSPLIT(A2,", ")
This returns North, East, and West across the row. If you specify only a comma, the spaces after the commas remain at the start of the second and third results. If that happens, either include the following space in the delimiter or clean the resulting text separately.
TEXTSPLIT can also place parts vertically by using its row-delimiter argument. Assumed layout: A2 contains red,blue,green, and B2:B4 are empty.
=TEXTSPLIT(A2,,",")
The results spill down B2:B4 as red, blue, and green. The blank argument tells Excel to omit the column delimiter and use the comma as the row delimiter. This is useful when a list should become a column rather than a row.
Handle repeated delimiters and empty parts
Consecutive delimiters can create empty results. For example, two commas in a row mean there is no text between those commas. That may be meaningful in a structured record, such as a missing middle field, so do not discard empty parts automatically if each position has a defined meaning.
When repeated spaces or punctuation are merely accidental and you want to ignore empty pieces, use TEXTSPLIT’s ignore_empty argument. Assumed layout: A2 contains red blue with two spaces between the words; B2 and the cells to its right are empty.
=TEXTSPLIT(A2," ",,TRUE)
The result is red in B2 and blue in C2, with no blank result between them. The fourth argument is TRUE to ignore empty items. This setting applies to empty pieces caused by the specified delimiter; it does not remove unrelated leading or trailing spaces from every result.
Split a column with Text to Columns
Text to Columns is a built-in wizard for splitting selected data into neighboring columns. In Excel for Microsoft 365 on Windows, select the source range, then choose Data > Text to Columns. In the wizard, choose Delimited when a character separates the parts, or Fixed width when the divisions are at consistent character positions.
- Select the cells containing the values you want to split. Avoid selecting unrelated columns.
- Choose Data > Text to Columns, select Delimited, and choose Next.
- Check the delimiter that matches your data, such as Comma, Tab, or Space. Select Other to enter a different character.
- Review the data preview. Adjust text qualifiers or delimiter selections if the preview does not show the intended columns.
- Choose Next, set a destination if you do not want to replace the source column, and select a data format if needed.
- Select Finish and inspect the resulting columns.
This operation can overwrite cells to the right of the selected source range. Before finishing, check that the destination columns are empty or choose a separate destination. If the source contains values such as postal codes with leading zeros, use the wizard’s column data format options to avoid converting them into numbers and losing those zeros. The same core wizard is available in Excel for Mac, though ribbon placement and dialog appearance can vary by release. Excel for the web may offer a different set of tools; if Text to Columns is not available in the interface you are using, use TEXTSPLIT where supported or open the workbook in desktop Excel.
Know what the split will and will not do
A delimiter split is literal: Excel looks for the character or sequence you choose. It does not automatically understand that a comma inside quotation marks may be part of a quoted field rather than a separator. For example, a properly structured CSV record can contain a quoted address with an internal comma. If you use a simple comma split on that text, the address may be broken into extra pieces.
For consistently formatted CSV or other imported files, Power Query is often a better choice because its import and transformation tools can account for structured data and let you review the parsed columns. A split also does not verify that each row has the same number of fields. If one row has an extra delimiter or a missing value, the output may have empty cells or a different number of parts. Review a sample of the source before applying the result to an entire dataset.
Text to Columns and TEXTSPLIT separate values; they do not remove the original delimiter from the source cell, validate the content, or decide how missing fields should be interpreted. Those decisions depend on what the data represents.
Power Query for repeatable imports
Power Query is useful when the same cleanup has to be repeated after importing refreshed data. In Excel for Windows, select a data range or table and use Data > From Table/Range to open the Power Query Editor. Select the column, then choose Home > Split Column > By Delimiter. Pick the delimiter and choose whether to split at each occurrence, at the left-most occurrence, or at the right-most occurrence, as appropriate for the data. The exact available options can vary with the chosen delimiter and Excel release.
After checking the preview, use Home > Close & Load to return the transformed result to Excel. When the source changes, refresh the query rather than repeating each cleanup step manually. Power Query is more setup than a one-off split, but the saved steps can make recurring imports more consistent. It is not necessary for a simple list that only needs to be split once.
Common problems and practical checks
- Unexpected spaces: Check whether the delimiter is a comma, a comma followed by a space, or just a space. The characters must match the source.
- #SPILL!: Clear cells in the intended output area or move the formula. Dynamic array results need space to expand.
- Parts are in the wrong columns: Preview the split and confirm that you selected the correct delimiter. A hyphen in a date or product code may not be a separator you want to use.
- Leading zeros disappear: Text to Columns may interpret a field as a number. Set that output column to Text in the wizard when preserving the exact characters matters.
- Some rows have extra pieces: Look for inconsistent source formatting, delimiters inside quoted text, or a delimiter included in only some records.
- Formula name is not recognized: TEXTSPLIT requires Microsoft 365 or Excel 2024. Use the wizard or Power Query in a version without the function.
Before replacing or distributing cleaned data, compare several output rows with the original values. If you need a static result from a formula, copy the spilled results and use Paste Special > Values in the destination. Keep a copy of the original data until you have confirmed the split is correct.
Frequently Asked Questions
Can Excel split text by a comma automatically?
Yes. Use TEXTSPLIT with a comma as the delimiter for a formula-driven result, or use Data > Text to Columns and select Comma for a one-time split. The formula updates from its source; Text to Columns changes the selected cells.
How do I split a cell by a space?
Use a space as the delimiter in TEXTSPLIT, or choose Space in the Text to Columns wizard. If there are multiple consecutive spaces, decide whether the blank fields should be preserved or ignored; TEXTSPLIT has an ignore_empty option for that case.
Can I split one cell into rows instead of columns?
Yes. In TEXTSPLIT, provide the separator as the row delimiter rather than the column delimiter. In the formula example above, the comma-separated items spill vertically below the formula cell.
Does Text to Columns keep the original value?
Not necessarily. It writes the split parts into the selected range and may overwrite neighboring cells. Choose a separate destination in the wizard if you want to retain the original column, or use a formula in an empty area.
Why does TEXTSPLIT return #SPILL!?
One or more cells where the result needs to appear are occupied, merged, or otherwise blocking the spill range. Clear the obstructing cells or move the formula to an area with enough empty space.
Related
- Excel Slicer: How to Change Its Data Source
- Excel Slicer: Change Font, Size, and Style in Excel
- Excel Slicer by Month: Set Up and Troubleshoot Date Filters
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Combine Quantity of the Same Item in ExcelTo combine quantities for repeated items in Excel, summarize each item with SUMIF or use a PivotTable. For…
- Calculate the Number of Months in ExcelTo count complete months between two dates in Excel, enter the earlier date in A2 and the later…
- Customer Call List Excel: Setup, Columns, and TipsYes. A customer call list in Excel is a structured worksheet for tracking who to contact, why, when,…
- Creating Labels in Word: Mail Merge, Setup, and PrintingTo create labels in Microsoft Word for Microsoft 365 on Windows, open Mailings > Start Mail Merge >…
- Excel Shortcut Keys Videos: What to Watch and LearnYes—Excel shortcut keys videos can show you how to use keyboard commands in real worksheets, and they are…
- Can You Open a Corrupted Excel File Online for Free?Sometimes you can open a corrupted Excel file online for free, but a browser-based viewer is not the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.