How to Use TEXTSPLIT in Excel
Splits one cell into many by a delimiter and spills the pieces into neighbouring cells — no Text to Columns needed.
What is TEXTSPLIT in Microsoft Excel?
TEXTSPLIT takes a delimited string and returns an array of the parts, spilling them across columns or down rows. It replaces the manual Data > Text to Columns workflow and, because it is a formula, it updates automatically when the source changes. It handles both column and row delimiters at once.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| text | Required | The delimited string to split. |
| col_delimiter | Required | The character that separates values across columns. |
| row_delimiter | Optional | An optional character that separates values down rows. |
| ignore_empty | Optional | TRUE skips empty results created by consecutive delimiters. |
| pad_with | Optional | The value used to fill gaps when rows have different lengths. |
When to Use TEXTSPLIT (Real-World Business Scenarios)
- 1
Splitting 'Rahul|North|55000' into three separate columns without leaving the formula bar.
- 2
Parsing a comma-separated tag list into individual rows for counting.
- 3
Building a dynamic table from a single imported string.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Packed Row | Part 1 | Part 2 | Part 3 |
|---|---|---|---|
| Rahul|North|55000 | Rahul | North | 55000 |
| Pooja|South|48000 | Pooja | South | 48000 |
| Aman|West|62000 | Aman | West | 62000 |
How it works: Splits the packed string at each pipe and spills the three parts into the cells to the right. Change the source and the split updates instantly.
Common Errors & How to Fix Them
Why it happens: The cells where the array wants to land already contain data.
Fix: Clear the neighbouring cells, or wrap the result in INDEX to pull out only the part you need.
Why it happens: The delimiter was not found in the source string, so no split occurred.
Fix: Confirm the exact delimiter with FIND, and remember that a space after a comma is a different character.
Try TEXTSPLIT Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (TEXTSPLIT)
What does the TEXTSPLIT formula do in Excel?
TEXTSPLIT takes a delimited string and returns an array of the parts, spilling them across columns or down rows. It replaces the manual Data > Text to Columns workflow and, because it is a formula, it updates automatically when the source changes. It handles both column and row delimiters at once.
What is the syntax for TEXTSPLIT?
What are common mistakes when using TEXTSPLIT?
#SPILL!: The cells where the array wants to land already contain data. (Clear the neighbouring cells, or wrap the result in INDEX to pull out only the part you need.) #N/A: The delimiter was not found in the source string, so no split occurred. (Confirm the exact delimiter with FIND, and remember that a space after a comma is a different character.)
Related Excel Formulas
Keep building your formula toolkit with these text functions.
- TEXTJOINJoins a whole range into one string with a delimiter you choose — the clean way to build comma-separated lists.
- TRIMRemoves leading, trailing and repeated spaces — the first fix for lookups that mysteriously return #N/A.
- SUBSTITUTEReplaces specific text inside a string — clean currency symbols, remove units or swap separators in bulk.
- LEFTExtracts the first characters of a string — pull a prefix, area code or year out of a longer code.
- LENCounts the characters in a cell, including spaces — the quickest way to catch invisible trailing whitespace.
- PROPERCapitalises the first letter of every word — fixes SHOUTED or all-lowercase names from raw imports.
Master TEXTSPLIT in Advanced Excel
Learn TEXTSPLIT along with our formula library, practice files, and verifiable certificates in our complete curriculum.