To split a cell string in Excel for Microsoft 365, enter =TEXTSPLIT(A2," ") in an empty cell to divide the text in A2 at each space, with the results spilling into neighboring cells. For a one-time split, use Data > Text to Columns; for data that needs repeated cleanup, consider Power Query.
What it means to split a cell
Splitting a cell means taking one text value and separating it into multiple values based on a delimiter or a position. For example, a cell containing Ada Lovelace could become Ada and Lovelace in adjacent columns. A value such as INV-2026-0042 could be divided at its hyphens.
Excel stores the original value in one cell; a split usually writes the parts into other cells rather than changing the original cell into several cells. Keep a copy of the source data if you may need to rebuild it. Also, splitting a string is not the same as unmerging a cell: an unmerged cell does not automatically divide its contents, and a merged cell’s value is stored in its upper-left cell.
The best method depends on whether the split is a one-off or must update as the source changes, and whether every row follows the same pattern. A consistent delimiter is easy to handle. Names, addresses, and other free-form text may need review because people can use different numbers of spaces or punctuation.
Split text with TEXTSPLIT
TEXTSPLIT is a dynamic-array function in Microsoft 365 and Excel 2024. It returns the separated parts in cells next to or below the formula cell, provided those spill cells are empty. The basic form is a source text followed by the delimiter. With the text Ada Lovelace in A2, the opening formula splits on a space and returns Ada in the formula cell and Lovelace in the next column.
With a product code in A2, the following formula separates the parts wherever a hyphen appears. For INV-2026-0042, the results are INV, 2026, and 0042 across three cells.
=TEXTSPLIT(A2,"-")
TEXTSPLIT can also split at more than one kind of delimiter. For example, if A2 contains a comma-separated or semicolon-separated entry, one option is to normalize the delimiters first, or use an array constant for the delimiter list. In the English-language Excel formula convention, this example splits at either comma or semicolon:
=TEXTSPLIT(A2,{",",";"})
Here the array constant provides two column delimiters. Regional settings can affect the punctuation Excel uses between formula arguments and inside array constants, so a formula copied from another locale may need separator changes. The function’s optional arguments also let you specify a row delimiter, ignore empty pieces, match delimiters case-insensitively, or provide a value to use where rows produce different numbers of results. Leave these options out unless your data calls for them.
To ignore empty pieces created by repeated delimiters, specify TRUE as the fourth argument. With Ada Lovelace in A2, the two adjacent spaces otherwise create an empty part between the names:
=TEXTSPLIT(A2," ",,TRUE)
The blank third argument leaves the row delimiter unspecified; TRUE tells Excel to ignore empty results. This is useful for repeated spaces, but it can also hide meaningful empty fields in structured data, so decide whether an empty part represents an error or a value you need to preserve.
Use Text to Columns for a one-time split
Text to Columns is a built-in wizard that writes results into worksheet cells. It is useful when you want to convert a selected column once, without leaving formulas behind.
- Make sure the columns to the right of the source are empty, or choose a different destination. Existing data can be overwritten.
- Select the cells to split, then choose Data > Text to Columns.
- Choose Delimited when a character such as a comma, tab, space, or hyphen separates the parts. Choose Fixed width when the text has consistent character positions.
- Set the delimiter or column breaks and inspect the preview. If relevant, choose a text qualifier for values enclosed in quotation marks.
- Set the destination if needed, select a data format for each output column when appropriate, and finish the wizard.
For example, to split North,East, select comma as the delimiter. If the source is a tab-separated export, select Tab instead. A delimiter such as Space can be risky for full names or addresses because it splits at every space, not just the boundary you intended. The preview helps reveal that before you commit the change.
Text to Columns is available in Excel for Microsoft 365 on Windows and Mac, though the dialog’s appearance can differ. In Excel for the web, the available data tools can differ from the desktop application; if the command is not available in your environment, use a formula or open the workbook in desktop Excel.
Choose the right approach
- Use TEXTSPLIT when the source may change and the result should recalculate automatically. It is also convenient when you want to preserve the original column.
- Use Text to Columns when you need a quick, static conversion and can verify that the output area is clear.
- Use Flash Fill when you can show Excel a recognizable pattern, such as extracting first names from a few examples. Enter the desired results in a neighboring column, then choose Data > Flash Fill. Review the output, especially when the source has exceptions; Flash Fill detects a pattern rather than applying a delimiter rule.
- Use Power Query when importing and cleaning a recurring data source. In Excel for Microsoft 365 on Windows, a typical route is Data > From Table/Range, followed in the Power Query Editor by Split Column > By Delimiter. Options and availability vary by platform and build.
These methods produce different kinds of results. A formula can remain linked to its source; Text to Columns and Flash Fill create values that generally need to be run again when the input changes. Power Query records transformation steps that can be refreshed as data is brought in again. Consider how the workbook will be maintained, not just how quickly the first split can be done.
Handle uneven or messy strings
Real-world text often does not match a clean example. A person might enter a middle name, omit a comma, use two spaces, or include the delimiter inside a quoted phrase. Before splitting a large range, inspect representative rows and decide what each separator means.
If some records have more parts than others, a formula may need to return a rectangular spill range. TEXTSPLIT has an optional padding argument for results where rows split into different numbers of columns. If no padding value is supplied, mismatched row widths can produce an error. You can also split one record at a time or standardize the source before splitting, depending on the task.
Do not assume a space is a reliable boundary between first and last names. Mary Ann Smith has three space-separated parts, and a person with a single name has only one. Similarly, splitting a street address at every comma may separate a city and region but may not resolve apartment numbers consistently. If the text has a business rule, apply that rule deliberately rather than treating every delimiter as equally meaningful.
CSV files deserve particular care. A comma inside a quoted field may be part of the field rather than a separator. Text to Columns includes a text-qualifier setting, but for complex or recurring CSV imports, Power Query is often a better place to inspect delimiters and quoted values. Always check a few output rows against the original record.
Prevent common errors
- Spill error: A dynamic-array formula cannot place results over occupied cells. Clear the cells in the expected output area or move the formula. Merged cells in the spill area can also prevent the results from appearing.
- Unexpected empty parts: Consecutive delimiters, such as two commas, create an empty field. Decide whether to preserve that blank or ignore empty pieces with TEXTSPLIT’s optional argument.
- Leading zeroes disappear: Excel may interpret a split result as a number. For identifiers such as
0042, set the relevant Text to Columns output column to Text, or preserve it as text with an appropriate formula-based workflow. - Dates or numbers change: Excel may interpret text as a date or numeric value when writing results. Inspect the preview and select Text for columns that must retain their literal characters.
- Data gets overwritten: Text to Columns writes into adjacent cells. Insert empty columns or select a safe destination before running it.
When a split result looks wrong, compare it with the original text and the exact delimiter. A hyphen, en dash, nonbreaking space, and regular space are different characters even if they look similar. If imported text contains unusual whitespace, cleaning or replacing that character may be necessary before a formula can split it as expected.
What happens to the original data?
TEXTSPLIT leaves its source cell untouched because it returns results elsewhere. That makes it suitable for a working view in which the raw import remains available. If you later copy formula results and use Paste Special > Values, the values no longer depend on the source formula.
Text to Columns writes the split pieces into the worksheet and can replace neighboring contents. It does not provide a continuing link back to the original text. Flash Fill similarly produces entries rather than a formula that recalculates from each source row. With any of these methods, save a copy or keep the raw data on a separate sheet when the source is important.
If the goal is to split a cell visually into two independently editable areas, Excel does not provide a way for one cell to become multiple cells while retaining the same grid position. Instead, split the text into neighboring cells, or use a layout with separate columns from the beginning.
Frequently Asked Questions
How do I split a cell in Excel by a space?
In Microsoft 365 or Excel 2024, with the text in A2, enter =TEXTSPLIT(A2," ") in an empty cell. For a one-time result, select the cells and use Data > Text to Columns, choosing Space as the delimiter.
Can I split text into rows instead of columns?
Yes. TEXTSPLIT accepts a row delimiter as its third argument. With A2 containing red|blue|green, use =TEXTSPLIT(A2,,"|") to spill the three items vertically. The empty second argument leaves the column delimiter unspecified.
Why does TEXTSPLIT show a spill error?
One or more cells where the results need to appear are not empty, or the spill range is blocked by a merged cell. Clear the obstructing cells or move the formula to an area with enough space.
Can Excel split text at more than one delimiter?
Yes. TEXTSPLIT can take an array constant containing multiple delimiters, such as comma and semicolon. For a static split, Text to Columns also lets you select multiple delimiter options in its wizard.
Does TEXTSPLIT work in older Excel versions?
TEXTSPLIT is available in Microsoft 365 and Excel 2024, not all older perpetual versions. If it is unavailable, use Data > Text to Columns for a one-time split, or use older text functions such as LEFT, MID, and FIND to build a formula suited to the specific pattern.
Can I split a cell without losing leading zeroes?
Yes. Treat identifier columns as text. In Text to Columns, select the relevant output column and set its data format to Text before finishing. For formulas, keep the result as text rather than converting the identifier to a number.
Related
- Excel Scatter Plot: How to Ignore Zero Values
- Excel Scatter Plot: How Blank Cells Are Handled
- Excel Scatter Plot From a Table: Steps and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IRR in Excel: Formula, Examples, and Common ErrorsYes. In Excel for Microsoft 365 on Windows, use the IRR function to estimate the return for a…
- Remove Duplicates in Excel Filter: A Step-by-Step GuideTo remove duplicate rows in Excel for Microsoft 365 on Windows, select a cell in your data, choose…
- How to Change Name Format in Excel: Formulas and Flash FillTo change names such as Ava Chen to Chen, Ava in Excel, enter a pattern with Flash Fill…
- Excel Formula to Calculate Percentage Change: ExamplesTo calculate percentage change in Excel, subtract the old value from the new value, then divide by the…
- How to Set an Absolute Cell Reference in Excel FormulasTo set an absolute cell reference in Excel, add a dollar sign before both the column letter and…
- Excel Top N Formula: Find the Highest Values and RowsTo return the top N numbers in Excel for Microsoft 365, use =LARGE(B2:B20,SEQUENCE(3)) to spill the three largest…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.