SPLIT Function Usage in Excel

Formulas & functions · Updated November 22, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.