In Excel for Microsoft 365, the split function is TEXTSPLIT, with the syntax TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). For example, with a comma-separated string in A2, enter =TEXTSPLIT(A2,",") in an empty cell to split it into columns; the results spill into neighboring cells.
What “split function” means in Excel
Excel does not have a worksheet function named SPLIT. People searching for “split function syntax” often mean Excel’s TEXTSPLIT, which separates text at one or more specified delimiters and returns the pieces in a dynamic array. It is available in Microsoft 365 and Excel 2024. Excel for the web also supports modern dynamic-array functions, although exact availability can depend on the account and update channel.
The name SPLIT belongs to functions in some other spreadsheet programs and programming environments, so formulas copied from those tools may not work in Excel. In Excel, use TEXTSPLIT for a formula-based split. If you need to separate data once rather than keep a formula connected to the source, Text to Columns is a built-in alternative.
A split is useful when a cell contains structured text, such as a full name, a comma-separated list, or a code whose parts are separated by hyphens. It separates the text; it does not automatically validate, clean, or interpret the resulting pieces.
TEXTSPLIT syntax and arguments
The function signature is TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). Square brackets mark optional arguments; do not type those brackets into a formula. The required arguments are the text to divide and the delimiter that marks column breaks.
textis the text or cell reference to split.col_delimiteris the character or text string that separates results into columns.row_delimiter, when supplied, separates results into rows instead.ignore_emptyis TRUE or FALSE. TRUE skips empty pieces caused by repeated delimiters; FALSE preserves them. FALSE is the default.match_modecontrols delimiter matching: 0 is case-sensitive and 1 is case-insensitive. The default is 0.pad_withsupplies a value for positions where a two-dimensional split would otherwise have missing cells. By default, those positions return#N/A.
You can omit an optional argument while still supplying a later one by leaving its position empty between commas. For instance, to set ignore_empty without setting a row delimiter, leave the third argument blank. Use commas as shown in the formulas below for standard US-style Excel syntax; some regional settings use different argument separators.
Basic example: split text into columns
Suppose A2 contains red,green,blue. With that text in A2, enter this formula in B2:
=TEXTSPLIT(A2,",")
The result is red in B2, green in C2, and blue in D2. You do not need to copy the formula across: Excel returns the pieces as a spilled array. The delimiter can be more than one character. If A2 contains North - South - East, with spaces around each hyphen, use the exact delimiter - (including the spaces) to avoid leaving stray spaces in the results.
For a delimiter that is a space, put a space between the quotation marks. With a full name in A2, this separates words at spaces, but a name with a middle name will produce three pieces rather than two. The function follows the delimiter pattern; it does not know which words represent first and last names.
Splitting into rows and handling repeated delimiters
Use row_delimiter when each separated item should occupy a new row rather than a new column. With a comma-separated list in A2, this formula entered in B2 returns the items vertically:
=TEXTSPLIT(A2,,",")
The blank second argument means no column delimiter was supplied; the comma in quotation marks is the row delimiter. A common use is turning a line of values into a vertical list for later filtering or lookup. If the source uses line breaks instead, and A2 contains line-break-separated text, use =TEXTSPLIT(A2,,CHAR(10)). The layout assumed here is the entire source string in A2; CHAR(10) represents a line feed commonly used in cell line breaks.
Repeated delimiters can create empty results. For example, a text value such as red,,blue contains an empty item between the two commas. If A2 contains that value and you want to discard the empty item, enter =TEXTSPLIT(A2,",",,TRUE). If empty positions carry meaning—for example, a missing field in a fixed-position record—leave ignore_empty as FALSE so the function preserves them.
Using several delimiters and matching options
When a list may use different separators, col_delimiter can be an array of delimiter values. With A2 containing text separated by commas or semicolons, enter this formula in B2:
=TEXTSPLIT(A2,{",",";"})
This asks Excel to treat either character as a separator. Array-constant punctuation can be affected by regional settings, so if the formula is rejected, check your Excel locale’s formula and array-constant separators. Another option is to normalize the source text first, replacing inconsistent separators with one consistent character.
By default, delimiter matching is case-sensitive. If the delimiter itself contains letters and the source may use different capitalization, set match_mode to 1. For example, with the text in A2 and the letter sequence “x” used as a delimiter regardless of case, enter =TEXTSPLIT(A2,"x",,FALSE,1) in B2. This setting changes how the delimiter is matched; it does not change the capitalization of the returned text.
Two-dimensional splits and uneven results
You can provide both a column delimiter and a row delimiter to split text across columns and rows. This is useful for data that has one kind of separator between fields and another between records. The two delimiters determine the shape of the returned array, so check a small sample before applying the approach to a large range.
Rows may not all produce the same number of columns. In that case, Excel fills the missing positions using pad_with; if you do not specify it, the default is #N/A. With A2 containing the source text, a formula can set a blank as the padding value: =TEXTSPLIT(A2,",",";",FALSE,0,""). Here commas separate columns, semicolons separate rows, empty pieces are preserved, and any missing positions are filled with an empty string. Choose a padding value deliberately: a blank can look like real missing data, while a visible marker can make uneven records easier to spot.
Spill behavior and where to place the formula
TEXTSPLIT is a dynamic-array function. Enter the formula in the top-left cell where you want the results to appear, then leave enough empty cells to its right or below. Excel displays a #SPILL! error if another value, a merged cell, or another obstruction occupies part of the required output area. Clear or move the obstructing content rather than trying to fill the formula into each destination cell.
Only the top-left cell contains the formula. You can refer to the complete result using the spill reference operator, such as B2# when the formula starts in B2. Avoid placing a spilling formula inside an Excel Table: spilled array formulas are not supported within table cells in the same way as ordinary worksheet ranges. Put the formula outside the table, or use a different method if the output must be part of a table.
Common errors and how to fix them
#NAME?: The Excel version may not supportTEXTSPLIT, the function name may be misspelled, or the formula may have been copied from a different spreadsheet program. Check availability and spelling.#SPILL!: One or more cells in the output area are blocked. Clear the cells or move the formula to an open range.- Unwanted spaces: The delimiter did not include spaces around punctuation. Match the actual separator, or clean the source values after splitting.
- Missing or extra empty pieces: Check for consecutive delimiters and decide whether
ignore_emptyshould be TRUE or FALSE. #N/Ain a split table: The rows may produce different numbers of columns. Specifypad_withif a different fill value is appropriate, or investigate the uneven records.
One important distinction: splitting changes how text is arranged, but it does not necessarily convert text that looks like a number or date into the underlying numeric or date value. If later calculations behave unexpectedly, check the resulting cell types and convert values as needed.
TEXTSPLIT versus Text to Columns and older Excel
Choose TEXTSPLIT when you want a formula-driven result that updates as the source cell changes. It is convenient for repeatable workbooks and supports splitting into rows as well as columns. For a one-time conversion, Text to Columns may be more straightforward: select the source range, then choose Data > Text to Columns on Excel for Windows. Follow the wizard to select a delimiter or fixed-width layout, preview the result, and choose a destination if you do not want to overwrite adjacent data.
Text to Columns writes values rather than a live formula result, so changes to the original text do not automatically redo the split. In Excel for Mac, use the Data tab’s Text to Columns command when available in the installed version; menu and ribbon layouts can differ slightly. Excel for the web has a more limited set of desktop data tools, so a formula may be the more practical option there.
In Excel versions without TEXTSPLIT, the Text to Columns wizard is a useful alternative for column splits. Other choices include Power Query for repeatable imports and transformations, or older formulas built from functions such as LEFT, MID, RIGHT, and FIND. Those formulas can work, but handling variable-length lists and many delimiters is usually more cumbersome. If a workbook must work on older Excel installations, verify compatibility before relying on a dynamic-array formula.
Frequently Asked Questions
Does Excel have a SPLIT function?
No. Excel’s worksheet function for splitting text is TEXTSPLIT. The name SPLIT is used in some other applications, but it is not the Excel function name.
What is the simplest TEXTSPLIT formula?
With the text in A2 and a comma between each item, enter =TEXTSPLIT(A2,",") in an empty cell. Excel returns the separated items across columns.
How do I split text into rows instead of columns?
With the source text in A2 and commas between items, use =TEXTSPLIT(A2,,","). The empty second argument leaves the column delimiter unused, while the third argument defines the row delimiter.
Why does TEXTSPLIT return #SPILL!?
Excel cannot place one or more results because cells in the output area are occupied or otherwise unavailable. Clear the obstructing cells or move the formula to an empty area.
Can I use TEXTSPLIT in older Excel versions?TEXTSPLIT requires Microsoft 365 or Excel 2024. For older releases, use Data > Text to Columns for a one-time column split, or consider Power Query or compatible text formulas.
Related
- Assembly Split in CATIA: How to Separate a Product
- Balance Sheet Accounting: How It Works
- J Curve Balance of Payments: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Month and YearIf the date is in A2, enter =TEXT(A2,"mmmm yyyy") in B2 to display its month and year, such…
- Excel Structured References: Tables, Formulas, and ExamplesA structured reference is an Excel formula reference that identifies data in a table by its table and…
- Excel Built-In String Functions and How to Use ThemExcel has built-in string functions—usually called text functions—that let you extract, search, clean, change, combine, and split text…
- Excel SUMIF: Sum Columns When a Cell Contains TextTo add values in one column only when the corresponding cell in another column is exactly x, use…
- Excel Filter Merged Cells: Why Only the First Row ShowsYes—when a cell is merged vertically across several rows, Excel stores its value only in the top-left cell,…
- Microsoft Excel COUNTIF Formula: Syntax and ExamplesIf product names are in A2:A100, count cells containing Apples with =COUNTIF(A2:A100,"Apples"). COUNTIF counts cells in a range…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.