Excel does not have a worksheet function named SPLIT; in Microsoft 365 and Excel 2024, use TEXTSPLIT to divide text into separate cells. With a comma-separated value in A2, enter =TEXTSPLIT(A2,","); for example, red,blue,green spills into three neighboring cells.
What “split function” means in Excel
People often search for an Excel “split function” when they want to separate a value at a comma, space, slash, line break, or other delimiter. The distinction matters: Excel’s function is called TEXTSPLIT, not SPLIT. It is a text-processing function, not a mathematical operation that divides a number into parts.
TEXTSPLIT is available in Microsoft 365 and Excel 2024. It returns a dynamic array, so one formula can fill several cells at once. Older Excel versions do not include TEXTSPLIT; alternatives include the Text to Columns command, Power Query, or older formulas built from functions such as LEFT, MID, and FIND.
Google Sheets does have a function named SPLIT, but its syntax and options differ from Excel’s TEXTSPLIT. A formula copied between the two spreadsheet programs may therefore need to be changed.
TEXTSPLIT syntax and arguments
The general syntax is =TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with]). The first argument is the text to divide. The column delimiter tells Excel where to split across columns; the optional row delimiter tells it where to split into rows.
- text: The text value or cell reference to split.
- col_delimiter: The character or text string that separates results across columns.
- row_delimiter: An optional separator that places results on separate rows.
- ignore_empty: If set to TRUE, consecutive delimiters do not produce empty results.
- match_mode: Set to 1 for a case-insensitive delimiter match; the default is case-sensitive.
- pad_with: The value used to fill gaps when a two-dimensional result has rows of different lengths. The default is #N/A.
Optional arguments can be left out, but commas still mark their positions. For example, to provide an ignore_empty setting while omitting row_delimiter, use two commas between the column delimiter and TRUE. This is a common source of formula-entry mistakes.
Split a value at a basic delimiter
Start with a consistent list in one cell. With the text red,blue,green in A2, enter =TEXTSPLIT(A2,",") in an empty cell. The result is red in the formula cell, blue in the next cell to the right, and green in the following cell. The comma is not included in the output.
TEXTSPLIT does not overwrite the original value. It returns separate results in neighboring cells, leaving the source text unchanged. This makes it useful when imported data needs to remain intact or when the original value may be needed for checking.
The delimiter must match the source data. If the values are separated by a semicolon, use a semicolon as the delimiter; if separated by a pipe, use the pipe character. For example, with North|East|West in A2, the formula =TEXTSPLIT(A2,"|") returns North, East, and West in adjacent cells.
Split into rows or use more than one delimiter
TEXTSPLIT can return results vertically as well as horizontally. With red,blue,green in A2, use =TEXTSPLIT(A2,,",") to omit the column delimiter and set the comma as the row delimiter. The three words spill down into three rows in one column. This can be handy when a list needs to become a vertical range for filtering or charting.
A source can also use different separators. If A2 contains tea,coffee;water, the formula =TEXTSPLIT(A2,{",",";"}) treats both the comma and semicolon as delimiters and returns tea, coffee, and water across columns. The array constant supplies two alternative delimiters. If your Excel regional settings use different formula separators, the characters used to separate formula arguments or array constants may vary.
When a data format uses a delimiter inside a value—for example, a comma inside a quoted address—TEXTSPLIT does not automatically interpret CSV quotation rules. A simple delimiter split may cut that value in the wrong place. For structured CSV imports, use Excel’s file import tools or Power Query rather than assuming every comma is a field boundary.
Handle repeated delimiters and extra spaces
Repeated separators can create empty results. For instance, red,,green contains two commas in a row, indicating an empty item between red and green. With red,,green in A2, =TEXTSPLIT(A2,",",,TRUE) ignores that empty item and returns red and green. Leave ignore_empty at its default when empty positions carry meaning, such as two consecutive separators representing a missing field.
Spaces immediately after delimiters remain part of the split values. If A2 contains red, blue, green, splitting at the comma returns values with leading spaces before blue and green. If those spaces are unwanted, TRIM can clean the resulting array. With that comma-and-space text in A2, use =TRIM(TEXTSPLIT(A2,",")); the results are red, blue, and green without leading spaces. TRIM also removes repeated ordinary spaces within text, so consider whether that behavior is appropriate for the data.
Other whitespace may need separate handling. A nonbreaking space copied from a web page, for example, is not always treated like an ordinary space. If a split appears correct but values do not match during a lookup, inspect the source for hidden or unusual characters instead of assuming the delimiter formula is wrong.
Practical uses and a worked example
TEXTSPLIT is useful when a cell stores several fields in a predictable order. Common cases include separating first and last names, breaking a product code into parts, or extracting list items from a delimited export. A split does not automatically understand what each item means: it only finds the delimiter and returns the text on either side.
Suppose A2 contains the text INV-2048-CA, where hyphens separate an invoice label, number, and region. With this value in A2, enter =TEXTSPLIT(A2,"-"). The result is INV, 2048, and CA in three cells across the row. The middle result is still text in many situations; if you need to calculate with it as a number, check the cell’s value and convert it deliberately, for example with VALUE when appropriate.
Before applying a split to a large list, check whether every row follows the same pattern. A value with a missing delimiter may return fewer pieces than expected, while extra delimiters may return more. If records have variable numbers of fields, plan how to handle those uneven results and blanks before building later formulas around fixed column positions.
Spill behavior and common errors
Because TEXTSPLIT returns a dynamic array, Excel needs empty cells in the direction and size of the result. If any destination cell contains data, the formula may show a #SPILL! error. Clear the obstructing cells or move the formula to an area with enough room. Hidden content, merged cells, or a spill area that extends beyond the worksheet can also interfere.
Entering a formula inside an Excel table is another important limitation: dynamic-array formulas cannot spill within table body cells in the usual way. Put the TEXTSPLIT formula in a normal worksheet range outside the table, or use another method to transform the table data. The formula cell should also be checked for accidental leading spaces or a missing equals sign if Excel displays the formula as text.
If Excel returns #VALUE! or another error, check that the delimiter is present as typed and that the arguments are in the correct order. A blank source cell, unexpected delimiter, or malformed formula can produce an unexpected result. Also confirm that the Excel version supports TEXTSPLIT; in an older version, Excel may not recognize the function.
TEXTSPLIT compared with other Excel methods
TEXTSPLIT is a strong choice when the source is text, the delimiter is known, and you want results that update when the source changes. It is especially convenient for repeatable cleanup because one formula can be filled down a list or copied alongside a data set.
For a one-time split, or when using a version without TEXTSPLIT, Excel’s built-in command may be simpler. Select the source cells, then choose Data > Text to Columns on Windows. Follow the wizard to choose a delimited or fixed-width split and preview the result. This command writes results into cells rather than returning a live formula result, so choose a destination carefully to avoid replacing existing data. Ribbon labels can vary slightly by platform or Excel build.
For repeatable imports and more involved transformations, Power Query can split columns as part of a refreshable data-cleaning process. In Microsoft 365 for Windows, select a cell in a source table or range and choose Data > From Table/Range to open the Power Query editor. Use its split-column tools, then load the transformed data back into Excel. Power Query is often more appropriate than a simple formula when importing recurring files or handling structured text with more complex rules.
TEXTBEFORE and TEXTAFTER can be useful when you only need the text before or after a delimiter rather than every part. They are available in Microsoft 365 and Excel 2024. Text to Columns, Power Query, and those functions solve related but different tasks; the best choice depends on whether you need a live formula, a one-time transformation, or a refreshable import.
Frequently Asked Questions
Does Excel have a SPLIT function?
No. Excel’s dynamic-array function for splitting text is TEXTSPLIT. SPLIT is a function name used in Google Sheets, where the syntax and behavior are not identical.
How do I split text into separate columns in Excel?
In Microsoft 365 or Excel 2024, enter TEXTSPLIT with the source cell and delimiter, such as =TEXTSPLIT(A2,",") for comma-separated text. Make sure the cells to the right are empty so the results can spill.
Can TEXTSPLIT put results in rows instead?
Yes. Leave the column-delimiter argument blank and supply the row delimiter, as in =TEXTSPLIT(A2,,","). The split items then spill vertically into rows.
Why does TEXTSPLIT show #SPILL!?
One or more cells needed for the result are not available, often because they contain data or are merged. Clear the spill range, or move the formula to an empty area outside a table.
What can I use if my Excel version lacks TEXTSPLIT?
For a manual split, use Data > Text to Columns. For recurring imports or more involved cleanup, use Power Query. Older formulas can also separate text, but they generally require more work and may need adjustment when the number of pieces varies.
Related
- Chemistry Balance Equation Worksheet: Practice and Answers
- Is the Balance of Payments Always Zero? Explained
- String Length in C++: size(), length(), and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel List Unique Entries with Formulas and Built-In ToolsTo list unique entries in Microsoft Excel for Microsoft 365, enter =UNIQUE(A2:A100) in an empty cell, replacing the…
- How to Consolidate Duplicate Values in Excel with FormulasTo combine repeated labels and total their numbers in Excel, create a PivotTable: select your data, choose Insert…
- Excel INDEX MATCH with Multiple Row CriteriaTo use INDEX MATCH with multiple row criteria in Excel, combine the MATCH function with an array expression…
- EDATE in Excel: Use It for Months, Not DaysExcel’s EDATE function moves a date forward or backward by a number of months, not by a number…
- Excel IF SUM: How to Combine IF, SUMIF, and SUMIFS FormulasTo test a total and return one result or another, combine IF with SUM: for example, with amounts…
- How to Divide in Excel: Formulas and ExamplesTo divide in Excel, enter a formula with a forward slash, such as =A2/B2, and press Enter. Replace…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.