What Is TEXTSPLIT?
The TEXTSPLIT function (available in Excel 365 and Excel 2021) splits a text string into multiple columns and/or rows based on specified delimiters. It simplifies tasks that previously required complex formulas using LEFT, RIGHT, MID, FIND, or the Text to Columns wizard.
Syntax
- text — The string you want to split.
- col_delimiter (optional) — Character(s) that separate items into columns.
- row_delimiter (optional) — Character(s) that separate items into rows.
- ignore_empty (optional) — TRUE to ignore empty results; FALSE (default) treats consecutive delimiters as empty cells.
- match_mode (optional) — 0 for case-sensitive (default), 1 for case-insensitive.
- pad_with (optional) — Value used to fill missing spots when the split creates a jagged array.
Why Use TEXTSPLIT?
TEXTSPLIT replaces the need for multiple helper columns or the Text to Columns wizard. It works dynamically inside a formula, updates automatically when source data changes, and can split both horizontally (columns) and vertically (rows) at once.
| Without TEXTSPLIT | With TEXTSPLIT |
|---|---|
| Text to Columns wizard (manual, static) | Single formula, fully dynamic |
| LEFT + RIGHT + MID + FIND nightmare | One clean function call |
| Helper columns everywhere | Spills into place automatically |
| Source data changes? Redo everything. | Source changes → results update instantly |
Easy Warming Up
Example 1: Splitting a Simple CSV String into Columns
Goal: Turn “apple,banana,cherry” into three separate cells across columns.
Formula:
If A2 contains apple,banana,cherry, the result spills into B2:D2 as:
| B2 | C2 | D2 |
|---|---|---|
| apple | banana | cherry |
Example 2: Splitting Text into Rows Using a Line Break
Goal: Convert a list separated by line breaks into a vertical column.
Assume A3 contains text with line breaks (CHAR(10)).
Formula:
The result spills down from B3:
| B3 |
|---|
| Red |
| Green |
| Blue |
Example 3: Splitting a Date String with Multiple Delimiters
Goal: Split “2026-05-22” into year, month, day.
Formula:
Result in B4:D4:
| B4 | C4 | D4 |
|---|---|---|
| 2026 | 05 | 22 |
Medium Getting the Hang of It
Example 4: Splitting Both Columns and Rows (Matrix)
Goal: Turn a semicolon-separated list of comma-paired values into a table.
A5 contains: Apple,Red;Banana,Yellow;Grape,Purple
Formula:
Result spills into a 3×2 block:
| Column1 | Column2 |
|---|---|
| Apple | Red |
| Banana | Yellow |
| Grape | Purple |
Example 5: Ignoring Empty Delimiters
Goal: Split “a,,c,,,” while ignoring empty entries.
Formula:
Result (ignoring empties):
| B6 | C6 |
|---|---|
| a | c |
If ignore_empty were FALSE, you would get empty cells for each consecutive delimiter.
Example 6: Using Pad_With to Align Jagged Splits
Goal: Split uneven lists so missing items are filled with a placeholder.
A7 contains: one,two;three,four,five
First group has 2 items, second has 3.
Formula:
Result:
| Col1 | Col2 | Col3 |
|---|---|---|
| one | two | N/A |
| three | four | five |
Hard Now We’re Cooking
Example 7: Nested TEXTSPLIT for Multi-Level Parsing
Goal: Extract key-value pairs from a string like “name=John;age=30;city=NY” and return two columns: Key and Value.
Step 1: Split by “;” to get each pair.
Step 2: Split each pair by “=”.
We can achieve this with a single formula using LET and TEXTSPLIT:
pairs, TEXTSPLIT(A8, “;”),
keys, INDEX(TEXTSPLIT(pairs, “=”), , 1),
vals, INDEX(TEXTSPLIT(pairs, “=”), , 2),
HSTACK(keys, vals)
)
Assuming A8: name=John;age=30;city=NY, the result spills:
| Key | Value |
|---|---|
| name | John |
| age | 30 |
| city | NY |
Example 8: Case-Insensitive Delimiter with Custom Padding
Goal: Split “Hello-WORLD-hello-World” using “-” as delimiter, ignore case, and pad missing cells with “?”.
Formula:
Because match_mode = 1, the delimiter is case-insensitive. Result:
| B9 | C9 | D9 | E9 |
|---|---|---|---|
| Hello | WORLD | hello | World |
If the string were “Hello–WORLD” (double dash), with ignore_empty FALSE you’d get an empty cell; setting it TRUE would skip it.
Example 9: Combining TEXTSPLIT with FILTER to Extract Specific Parts
Goal: From a comma-separated list, return only items that contain the letter “a”.
A10 contains: apple,banana,cherry,date,fig
Formula:
Result spills:
| B10 | C10 | D10 |
|---|---|---|
| apple | banana | date |
Tips and Best Practices
- TEXTSPLIT returns a dynamic array; ensure enough empty cells below and to the right for the spill.
- Use
TRIMinside TEXTSPLIT if you need to remove extra spaces:=TEXTSPLIT(TRIM(A1), ","). - When dealing with multiple delimiters, nest TEXTSPLIT or use
TEXTJOIN+TEXTSPLITas shown in the advanced examples. - Remember that
ignore_emptydefaults to FALSE; set to TRUE when you want to skip empty fields. - For splitting by more than one character (e.g., ”
&&“), supply the exact string as delimiter.
💡 PRO TIP
TEXTSPLIT + LAMBDA = Unstoppable
Wrap your TEXTSPLIT logic in a LAMBDA and suddenly you’ve got a reusable parser you can call anywhere. Imagine =PARSEKV(A1, ";", "=") that splits any key=value string into a clean table. Define it once in Name Manager, use it everywhere.
Conclusion
The TEXTSPLIT function is a powerful addition to Excel’s text-manipulation toolbox. By mastering its arguments — delimiters, ignore_empty, match_mode, and pad_with — you can efficiently parse complex strings into structured data without resorting to lengthy formulas or manual steps.
Go split some text. You’ve got this.
Leave a Reply
You must be logged in to post a comment.