Quick Navigation
TEXTSPLIT Function
Summary
The TEXTSPLIT function dynamically divides text strings into arrays using specified column and row delimiters. It provides a formula-based alternative to the Text-to-Columns feature, enabling flexible data separation across columns or rows.
Syntax
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| text | text |
Yes | Required text string containing delimiters to split |
| col_delimiter | text/array |
Yes | Text constant or array marking column splits |
| row_delimiter | text/array |
No | Optional text constant or array for row splits |
| ignore_empty | TRUE/FALSE |
No | Skip empty segments from consecutive delimiters |
| match_mode | 0/1 |
No | Case matching: 0=exact, 1=ignore case |
| pad_with | any |
No | Replacement value for missing array elements |
Using the TEXTSPLIT Function
TEXTSPLIT transforms delimited text into structured arrays, perfect for parsing names, addresses, CSV data, or log entries. Use it to automate data cleanup that previously required manual Text-to-Columns operations.
Common TEXTSPLIT Examples
Split Full Name into First/Middle/Last
=TEXTSPLIT(A1," ")Converts "Dakota Lennon Sanchez" into three columns: Dakota | Lennon | Sanchez
Parse CSV-like Data into Table
=TEXTSPLIT(A1,",",";")Transforms "1,2,3;4,5,6" into 2x3 array: 1|2|3 4|5|6
Multiple Delimiters with Quote Handling
=TEXTSPLIT(A2,{".","-"})Splits "Do. Or do not. There is no try. -Anonymous" by period and dash
Row-wise Splitting Only
=TEXTSPLIT(A3,,".")Splits sentence into rows using periods as row delimiter
Ignore Empty Cells with Custom Padding
=TEXTSPLIT(A4,{" ","."},,TRUE,0)Skips empty results and fills with 0 instead of #N/A
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: col_delimiter missing or invalid
Solution: Ensure col_delimiter provided as text or array constant
#N/A in results
Cause: Array larger than available data segments
Solution: Use pad_with or ignore_empty=TRUE
No spill possible
Cause: Target range occupied by data
Solution: Clear space for dynamic array spill
Notes
- Available only in Excel 365 and Excel 2021+
- Returns dynamic array that spills automatically
- Use array constants {} for multiple delimiters
- Opposite of TEXTJOIN which combines arrays
- Perfect companion for dynamic array functions
Compatibility
Available in: Excel 365, Excel 2021
Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021+