The SPLIT function does not exist in Excel; the correct way to split text in Excel is to use the TEXTSPLIT function (Microsoft 365 and Excel 2024) or the older Text to Columns wizard. For example, to split “apple,banana,cherry” by commas, enter =TEXTSPLIT(A1,",") in a cell, and it will spill the three items across separate cells.
What Is TEXTSPLIT and When Did It Arrive?
TEXTSPLIT is a dynamic array function introduced in Microsoft 365 and Excel 2024. It takes a text string and a delimiter (or multiple delimiters) and splits the text into rows or columns. Unlike the legacy Text to Columns feature, TEXTSPLIT is a live formula—if the source text changes, the split results update automatically. It belongs to the same family as TEXTBEFORE, TEXTAFTER, and other text functions added in the 2020s.
How to Use TEXTSPLIT: Syntax and Examples
The syntax is: =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [pad_with])
- text: the cell reference or string to split (required).
- col_delimiter: the character(s) that separate items into columns (required).
- row_delimiter: optional; if provided, splits text into multiple rows as well.
- ignore_empty: optional; TRUE or FALSE (default FALSE) to skip empty values.
- pad_with: optional; a value to fill in if the split produces a jagged array (e.g., different row lengths).
Example: With the text “red,green,blue” in cell A1, =TEXTSPLIT(A1,",") returns “red” in B1, “green” in C1, “blue” in D1. If you use a semicolon as a row delimiter, like =TEXTSPLIT("a;b,c;d",",",";"), you get a 2×2 grid: a and b in the first row, c and d in the second row.
Using Text to Columns (Legacy Method)
Before TEXTSPLIT, the standard way to split text was the Text to Columns wizard. Select the column with the text, go to Data > Data Tools > Text to Columns, choose Delimited or Fixed width, then pick the delimiter (comma, tab, semicolon, or custom). This method overwrites the original data unless you copy the column first. It is static—no formula updates. It still works in all Excel versions, including Excel 2010 and later.
How to Choose Between TEXTSPLIT and Text to Columns
- Use TEXTSPLIT when you need a dynamic result that updates when the source changes, or when you want to keep the original data intact.
- Use Text to Columns when you need a one-time conversion, are working in an older Excel version without TEXTSPLIT, or need to split into a fixed number of columns with complex delimiters (like multiple spaces).
- For splitting into rows (vertical), TEXTSPLIT is the only formula option; Text to Columns only splits into columns.
Common Problems and How to Fix Them
Problem: You get a #VALUE! error. This usually happens when the delimiter is not found in the text. For example, =TEXTSPLIT("apple,banana",";") returns #VALUE! because there is no semicolon. Fix: check the delimiter and ensure it matches the text.
Problem: The result spills over and overwrites existing cells. Dynamic arrays require empty space. If you see a #SPILL! error, clear the cells that block the output, or use the @ operator to return only the first result (though that defeats the purpose).
Problem: You want to split by multiple different delimiters (e.g., comma and semicolon). Use an array constant: =TEXTSPLIT(A1,{",",";"}). This splits by either delimiter.
Comparison: TEXTSPLIT vs. Other Text Functions
TEXTSPLIT is different from LEFT, RIGHT, and MID, which extract a specific number of characters from a position. TEXTSPLIT is also different from FIND and SEARCH, which return positions. TEXTBEFORE and TEXTAFTER extract text before or after a delimiter, but they return a single piece, not an array. TEXTSPLIT is the only function that returns multiple values in one shot.
Frequently Asked Questions
Can I use SPLIT in Excel? No, there is no SPLIT function. The closest is TEXTSPLIT in Microsoft 365/Excel 2024, or Text to Columns for a one-time split.
How do I split text into multiple rows? Use TEXTSPLIT with a row delimiter. For example, =TEXTSPLIT("a;b;c",,";") splits by semicolon into rows (A1, A2, A3). Note the comma before the row delimiter—the column delimiter is omitted.
Does TEXTSPLIT work in Excel 2019? No, TEXTSPLIT requires Microsoft 365 or Excel 2024. In older versions, use Text to Columns or a combination of LEFT, MID, and FIND.
How do I split by newline character? Use CHAR(10) as the delimiter: =TEXTSPLIT(A1,CHAR(10)). This splits text at line breaks.
Can I split text into both rows and columns at once? Yes, provide both column and row delimiters. Example: =TEXTSPLIT("a,b;c,d",",",";") creates a 2×2 array.
Alternatives and Workarounds for Older Excel
If you don’t have TEXTSPLIT, you can use the Text to Columns wizard, or write a formula using MID and FIND to extract each piece. For a comma-separated list in A1, the first item is =LEFT(A1,FIND(",",A1)-1), the second is =MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-1), and so on—though this gets messy with many items. A cleaner alternative is to use Power Query (Get & Transform) to split a column by delimiter, which is available in Excel 2016 and later.
Related
- SQL String Split: How to Separate Delimited Values
- Java String.split(-1): Keep Trailing Empty Fields
- Visual Basic String Split: Syntax, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Table Filter Button Greyed Out? Fixes That WorkIf Excel’s filter button is greyed out, first press Esc to leave cell-edit mode, ungroup any selected worksheets,…
- Excel Formula for List of Unique Values: 10 Ways That WorkFor a basic list of distinct entries in Microsoft 365, with source values in A2:A20, enter =UNIQUE(A2:A20) in…
- Excel Filter Shortcut: How to Filter Data Quickly in ExcelIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn filters on or off for a selected…
- Find a Position in Excel with MATCH and XMATCH FormulasTo find where a value sits within a list, use MATCH or XMATCH. If the value to find…
- Excel Formula for Fiscal Year: Step-by-Step GuideThe simplest Excel formula for determining a fiscal year is =YEAR(A2)+(MONTH(A2)>=FiscalStartMonth), where A2 contains the date and FiscalStartMonth…
- Excel Formula for Calendar Days Between DatesTo calculate calendar days between two dates in Excel, subtract the earlier date from the later one: with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.