In Microsoft 365 or Excel 2024, use TEXTSPLIT to divide text from one cell into separate cells. If A2 contains Mira Chen, Sales, West, with a comma and space between each part, enter =TEXTSPLIT(A2,", ") in an empty cell; Excel returns the three parts across the row.
What TEXTSPLIT does
TEXTSPLIT looks for a delimiter—a character or string that marks where one piece of text ends and the next begins—and separates the cell’s contents at each match. Common delimiters include commas, spaces, hyphens, slashes, tabs, and line breaks. The source cell stays unchanged; the formula returns the separated values in neighboring cells.
For a comma-separated list, the delimiter you choose matters. With A2 containing red, green, blue, splitting on a comma alone leaves a leading space before “green” and “blue.” Splitting on comma followed by a space treats that pair as the delimiter and returns clean values. The delimiter must match the data, including spaces and punctuation.
TEXTSPLIT is a dynamic-array function. It places its results in a block of cells automatically, so you normally enter the formula once rather than copy it across columns. This behavior is often called spilling. It is convenient for lists with a consistent structure, but the cells where results need to appear must be available.
Basic formula and delimiter choices
The second argument specifies the column delimiter: the text that divides values into columns. With the example data in A2, this formula splits on the comma-and-space sequence:
With Mira Chen, Sales, West in A2, enter this formula in B2. It returns Mira Chen, Sales, and West in B2:D2.
=TEXTSPLIT(A2,", ")
If the source uses a pipe with spaces on both sides, match that delimiter instead. With North | Central | South in A2, this formula returns the three region names without the surrounding spaces:
=TEXTSPLIT(A2," | ")
When records use inconsistent spacing, split on the punctuation and clean the returned values separately. For example, if A2 contains Mira, Sales, West, this formula splits at every comma, and the TRIM function removes leading and trailing ordinary spaces from each result:
=TRIM(TEXTSPLIT(A2,","))
TRIM removes extra spaces around text and reduces repeated internal ordinary spaces to one. It does not remove every kind of invisible character; a nonbreaking space copied from a web page may need separate cleanup. Also, a space can be meaningful data, so do not trim automatically if the exact text must be preserved.
Split text into rows instead of columns
TEXTSPLIT can use a row delimiter as well as a column delimiter. This is useful when one cell contains a list separated by line breaks—for instance, values pasted into a cell with Alt+Enter. In the formula, the empty argument between the commas leaves the column delimiter unspecified, while CHAR(10) represents a line-feed character.
With line-break-separated entries in A2, enter this formula in B2. It returns one item per row, spilling downward:
=TEXTSPLIT(A2,,CHAR(10))
Make sure the cells below the formula are clear so the result can spill. If the cell contains a different kind of line break, such as a carriage return, CHAR(10) may not match it. Pasted data can include mixed line endings, so inspect a sample before applying the formula to a large range.
Handle repeated or missing delimiters
Repeated delimiters create empty fields unless you tell TEXTSPLIT to ignore them. This distinction matters when empty fields represent real information. For example, in a comma-delimited record, North,,West could mean that the middle field is intentionally blank. Keeping the empty result preserves the position of each field; ignoring it shifts “West” into the second position.
With North,,West in A2, this formula ignores empty fields and returns only North and West:
=TEXTSPLIT(A2,",",,TRUE)
The fourth argument is ignore_empty. Set it to TRUE only when empty pieces should be discarded. If the delimiter does not occur at all, TEXTSPLIT generally returns the original text as one result rather than inventing multiple fields. Decide how missing delimiters should be treated in your workflow, especially when a downstream calculation expects a fixed number of columns.
Use TEXTBEFORE or TEXTAFTER for one split
If you need only the text before the first delimiter or after it, rather than all the pieces, TEXTBEFORE and TEXTAFTER can be more direct. They are available in Microsoft 365 and Excel 2024. They do not replace TEXTSPLIT when the goal is to return every field from a longer list.
With Mira Chen, Sales, West in A2, this formula returns Mira Chen, the text before the first comma-and-space:
=TEXTBEFORE(A2,", ")
With the same value in A2, this formula returns Sales, West, everything after the first comma-and-space:
=TEXTAFTER(A2,", ")
These functions are handy when only the first component has a special meaning, such as extracting a name before a separator while keeping the remainder together. If there may be no delimiter, consider how you want the formula to respond; a missing delimiter can produce an error with these functions.
Options in older Excel versions
TEXTSPLIT is available in Microsoft 365 and Excel 2024, but not in Excel 2021 or earlier perpetual releases. If a workbook reports #NAME? for TEXTSPLIT, check which Excel version is opening it. For an older version, the Data tab’s Text to Columns command can split a selected column without a formula, or you can use older text functions for a simple, known delimiter.
For a basic comma-separated value, LEFT and FIND can return the text before the first comma. With Mira, Chen in A2, this formula returns Mira:
=IFERROR(LEFT(A2,FIND(",",A2)-1),A2)
With the same value in A2, this formula returns the text after the first comma, including any space that follows it:
=IFERROR(MID(A2,FIND(",",A2)+1,LEN(A2)),"")
These examples split at only the first comma. They do not automatically create a flexible set of columns for every item in a list, and they can become cumbersome when you need the second or third delimiter. IFERROR makes the first formula return the original text when no comma exists; the second returns an empty string in that case. These choices are examples, not universal rules—use a missing-delimiter result that suits the data.
Use Text to Columns for a one-time split
When you want to separate existing values once rather than keep a formula linked to the original cell, Text to Columns is a practical alternative. In Excel for Microsoft 365 on Windows:
- Select the cells containing the text. For safety, work on a copy of the source column if you need to retain the original values.
- Choose Data > Text to Columns.
- Select Delimited and choose Next.
- Choose the delimiter, such as comma, or select Other and enter the relevant character. Review the preview to confirm where Excel will divide the text.
- Choose Next, set the destination if you do not want the results to replace nearby cells, and select Finish.
The command writes values into cells; it does not create a formula that recalculates when the original text changes. Check the destination carefully because existing content in the output area can be overwritten. Excel for Mac also provides Text to Columns on the Data tab, although the exact ribbon layout can differ by release. The steps and dialog labels may vary slightly.
Spill errors and other common problems
If a TEXTSPLIT result does not appear, the formula may be blocked by occupied cells. A dynamic array cannot spill into cells that contain data, merged cells, or certain other obstructions. Excel may show #SPILL!; select the formula cell and inspect the highlighted spill range, then clear or move anything blocking it. Leave enough space both horizontally and vertically for the largest expected result.
Other frequent issues include:
- The delimiter does not match: A formula splitting on comma-space will not split on a comma followed by a tab. Compare the delimiter in the formula with the actual characters in the source.
- Unexpected spaces remain: Split on the full delimiter, or use TRIM where removing ordinary leading and trailing spaces is appropriate.
- Some rows have a different number of fields: The spilled results may occupy different widths. Keep destination cells clear and decide how shorter rows should be handled in later calculations.
- Excel does not recognize the function: Confirm that the file is open in Microsoft 365 or Excel 2024, rather than an older version.
- Text appears to be one value after splitting: Verify that the displayed separator is not a visually similar character, such as a different dash or a nonbreaking space.
Excel’s formula argument separator can also depend on regional settings. The formulas here use commas, as in standard English-language Excel installations. If your Excel expects semicolons between function arguments, use the separator required by that installation; do not confuse the argument separator with the delimiter inside the quoted text.
Choose a method that fits the task
Use TEXTSPLIT when the source data may change and you want the separated results to update automatically. It is particularly useful for repeatable cleaning steps and lists with a consistent delimiter. Use Text to Columns for a one-time conversion or when you want to inspect a preview and write fixed values into cells. Use TEXTBEFORE or TEXTAFTER when you need just one side of a separator, and older functions such as LEFT, FIND, and MID when compatibility with earlier Excel releases is essential.
Before splitting a whole column, test a few representative rows. Include examples with missing values, repeated delimiters, extra spaces, and punctuation inside a field. A comma-separated line is not necessarily a simple comma-delimited record: if a field itself contains a comma, the data may use quotation marks to keep that field together. TEXTSPLIT separates on the delimiter you specify; it is not a full parser for every structured text format. For quoted CSV data, use a suitable import workflow rather than assuming that a plain comma split will preserve field boundaries.
Frequently Asked Questions
What is the formula to split text by a comma in Excel?
In Microsoft 365 or Excel 2024, if the text is in A2, use =TEXTSPLIT(A2,","). If spaces after commas should not remain at the start of results, split on comma-space when that sequence is consistent, or clean the results with TRIM.
Can Excel split text into separate rows?
Yes. TEXTSPLIT accepts a row delimiter as its third argument. For entries separated by line breaks in A2, use =TEXTSPLIT(A2,,CHAR(10)). Make sure the output cells below the formula are empty so the results can spill.
Why does TEXTSPLIT show #NAME?
The function may not be available in the Excel version opening the workbook. TEXTSPLIT requires Microsoft 365 or Excel 2024. In older Excel, use Text to Columns for a one-time split or build a formula with functions such as LEFT, FIND, and MID for a simple case.
Can I split text at a space?
Yes. If A2 contains Jordan Lee and the space is the intended separator, use =TEXTSPLIT(A2," "). This splits at every ordinary space, so it can create extra pieces when names or phrases contain multiple spaces.
How do I keep empty fields when splitting?
By default, TEXTSPLIT preserves empty results caused by repeated delimiters. Do not set its ignore_empty argument to TRUE if blank fields need to keep their original positions. If you do want to discard empty pieces, set that argument to TRUE.
Practical check before filling a workbook
After confirming the formula on sample rows, check that the split columns mean what their headers say and that no source values have been silently altered. If results will feed a report, lookup, or import, verify a few outputs against the original text and confirm the expected number of fields. A correct split depends on the structure of the input: the formula can separate at a delimiter, but it cannot determine whether that delimiter is meaningful without a clear rule.
Related
- Excel Solver for Data Analysis: Setup, Models, and Limits
- Cash Flow Excel Sheet: Build a Reliable Monthly Forecast
- Cash Flow Format in Excel: Build a Simple Statement
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMPRODUCT in Excel: How Its Array Calculations WorkIn Excel, SUMPRODUCT multiplies corresponding values in one or more arrays and adds the resulting products. It is…
- XLOOKUP Showing Formula Instead of Result?If XLOOKUP displays the formula itself instead of a result, first turn off Show Formulas on the Formulas…
- SUMIFS Excel Criteria Range: How to Set It UpIn Excel, a SUMIFS criteria range is the range of cells that SUMIFS checks against a condition before…
- Excel Text Comparison: Formulas, Case, Spaces, and ErrorsWith the text values in A2 and B2, compare them in Excel using =A2=B2 for a case-insensitive check…
- Excel Query Function: What to Use InsteadExcel does not have a worksheet function named QUERY like Google Sheets does. In Microsoft 365 for Windows,…
- Excel Date Minus 1 Year: Formula and Leap-Year RulesTo subtract one calendar year from a date in Excel, use =EDATE(A2,-12), assuming the original date is in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.