TEXTSPLIT divides a text string into separate columns and rows using the delimiters you specify. It returns a dynamic array that spills into neighbouring cells, making it easy to separate names, addresses, CSV data, and more.
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])What does TEXTSPLIT do?
TEXTSPLIT is a Text function that takes one piece of text and breaks it apart at the characters you choose. You tell it which character separates columns and, optionally, which character separates rows, and it returns a block of values that spills into the cells around the formula. For example, splitting "North,1200" with a comma gives you North in one cell and 1200 in the next.
Before TEXTSPLIT, separating text usually meant combining LEFT, RIGHT, MID, FIND, and SUBSTITUTE, or using Text to Columns, which is a manual step. TEXTSPLIT does the job with one formula that updates automatically when the source text changes. That matters when you receive data as a single column of combined values and need it in a clean table for sorting, filtering, or calculations.
A common situation: a colleague sends you a list where each cell contains a full name and department, such as "Ava Patel, Finance". You need first name, last name, and department in separate columns. With TEXTSPLIT you write one formula next to the data, and the results spill across three columns. You can then sort, filter, or use the values in other formulas without retyping anything.
When to use TEXTSPLIT
- Separating first and last names stored in one cell.
- Splitting comma-separated values (CSV) pasted into a single column.
- Breaking a full address into street, city, and postcode.
- Dividing a product code such as
AB-1234-REDinto its parts. - Turning a list of tags separated by semicolons into individual cells.
- Splitting dates and times that arrive as one text value.
TEXTSPLIT syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])| Argument | Required? | What to enter |
|---|---|---|
text | Required | The text value or cell reference you want to split. Example: A2 where A2 contains North,1200. |
col_delimiter | Required | The character or characters that mark where a new column starts. Example: "," to split at each comma. |
row_delimiter | Optional | The character or characters that mark where a new row starts. Example: ";" to start a new row at each semicolon. Leave blank if you only need columns. |
ignore_empty | Optional | TRUE or FALSE. When TRUE, empty values caused by two delimiters in a row are skipped. Example: TRUE turns a,,b into two cells instead of three. |
match_mode | Optional | 0 for case-sensitive matching, 1 for case-insensitive matching. Example: 1 treats x and X as the same delimiter. |
pad_with | Optional | The value used to fill cells when rows have different numbers of columns. Example: "" fills missing values with an empty string. |
What it returns: A dynamic array of text values arranged in columns and rows. The result spills into the cells to the right and below the formula cell. If the text contains no delimiter, the original text is returned as a single value.
TEXTSPLIT examples
1 Split a full name into first and last name
The goal is to separate each full name in column A into first and last names. The formula =TEXTSPLIT(A2," ") uses a space as the column delimiter, so it breaks the text at every space. For Ava Patel, it returns Ava in the formula cell and Patel in the cell to its right. Because the formula is entered once in D2, the result spills across D2:E2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Full Name | Department | Score | ||
| 2 | Ava Patel | Finance | 88 | Ava | |
| 3 | Liam Chen | Sales | 92 | ||
| 4 | Noah Kim | Support | 75 | ||
| 5 | Mia Lopez | Finance | 81 | ||
| 6 |
2 Split comma-separated values into columns
Here each cell in column A contains a region and a sales figure separated by a comma. The formula =TEXTSPLIT(A2,",") splits at the comma and returns two values side by side. The first value is the region name and the second is the sales number as text. The result spills into D2:E2, and you can copy the formula down for the remaining rows.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | RegionData | Quarter | Notes | ||
| 2 | North,1200 | Q1 | OK | North | |
| 3 | South,950 | Q1 | OK | ||
| 4 | East,1430 | Q2 | Review | ||
| 5 | West,1100 | Q2 | OK | ||
| 6 |
3 Split an address into street, city, and postcode
Each address in column A uses a comma between the street and the city, and a space before the postcode. The formula =TEXTSPLIT(A2,", ") uses the two-character delimiter comma plus space, so it splits at , only. For 12 Oak Road, Leeds LS1 4AB, it returns 12 Oak Road and Leeds LS1 4AB. The result spills into D2:E2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Address | Customer | OrderID | ||
| 2 | 12 Oak Road, Leeds LS1 4AB | Ava Patel | 1001 | 12 Oak Road | |
| 3 | 5 Mill Lane, Manchester M2 3BC | Liam Chen | 1002 | ||
| 4 | 88 High Street, Bristol BS1 5XY | Noah Kim | 1003 | ||
| 5 | 3 Park Avenue, York YO1 2ZZ | Mia Lopez | 1004 | ||
| 6 |
4 Split product codes into parts
Product codes in column A follow the pattern AB-1234-RED, with hyphens separating the parts. The formula =TEXTSPLIT(A2,"-") splits at each hyphen and returns three values: the category, the number, and the colour. The result spills across D2:F2. This makes it easy to sort by colour or filter by category without changing the original code.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ProductCode | Price | Stock | ||
| 2 | AB-1234-RED | 19.99 | 45 | AB | |
| 3 | CD-5678-BLU | 24.5 | 12 | ||
| 4 | EF-9012-GRN | 15 | 30 | ||
| 5 | GH-3456-YEL | 22.75 | 8 | ||
| 6 |
5 Split tags separated by semicolons into rows
Column A contains a list of tags separated by semicolons, such as urgent;finance;review. The formula =TEXTSPLIT(A2,";") splits at each semicolon and returns the tags in separate columns. For this row, it returns urgent, finance, and review across D2:F2. You can then use the spilled values in filters or counts.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tags | Ticket | Priority | ||
| 2 | urgent;finance;review | T-100 | High | urgent | |
| 3 | sales;followup | T-101 | Medium | ||
| 4 | support;billing;urgent | T-102 | High | ||
| 5 | review | T-103 | Low | ||
| 6 |
Common TEXTSPLIT errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The col_delimiter or row_delimiter argument is missing or empty when the text does contain a delimiter. | Provide a delimiter as a text string in quotes, such as "," or " ". If you truly want no split, use a delimiter that does not appear in the text. |
| #SPILL! | The cells where the result needs to spill are not empty. | Clear the cells to the right and below the formula, or move the formula to an area with enough free space. |
| #N/A | The match_mode argument is invalid or the text argument refers to a value that cannot be split as expected. | Use 0 or 1 for match_mode, and confirm the text argument points to a cell containing text. |
| #NAME? | The function name is misspelled or your Excel version does not support TEXTSPLIT. | Check the spelling as TEXTSPLIT. If you see #NAME?, update Excel to a version that includes dynamic array text functions. |
| #CALC! | The formula returns an empty array, often because ignore_empty is TRUE and the text contains only delimiters. | Set ignore_empty to FALSE, or check that the source text contains actual values between delimiters. |
Tips for using TEXTSPLIT
- Use a two-character delimiter such as
", "to split at a comma followed by a space and avoid extra spaces in the results. - Set ignore_empty to TRUE when your data may contain double delimiters, so you do not get blank cells.
- Use pad_with to fill shorter rows when splitting text that has both column and row delimiters.
- Remember that TEXTSPLIT returns text; use VALUE or multiply by 1 if you need numbers for calculations.
- Place the formula in a cell with enough empty space to the right and below for the spill range.
Frequently asked questions
What does TEXTSPLIT do in Excel?
TEXTSPLIT splits one text value into multiple cells using the delimiters you specify. It can create columns, rows, or both, and the result spills into the surrounding cells.
Can TEXTSPLIT split by more than one delimiter?
Yes. You can provide a col_delimiter and a row_delimiter. For multiple alternative delimiters in the same direction, use an array constant such as {",",";"}.
Why does TEXTSPLIT return #SPILL!?
The spill range is blocked by existing data. Clear the cells to the right and below the formula, or move the formula to an empty area.
Does TEXTSPLIT work in Excel 2019?
No. TEXTSPLIT is available in Microsoft 365 and newer Excel versions that support dynamic arrays. In older versions, use Text to Columns or a combination of LEFT, RIGHT, and FIND.
How do I keep empty values when splitting?
Set ignore_empty to FALSE. By default, or when TRUE, consecutive delimiters are treated as one and empty values are skipped.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. TEXTSPLIT reference