The TEXTSPLIT function splits the text in a cell by a delimiter and spills the parts into neighboring cells: =TEXTSPLIT(A2," ") turns “John Michael Smith” into three columns, and =TEXTSPLIT(A2,,", ") puts each item of a comma-separated list on its own row. It is the formula version of the Text to Columns wizard, so the result updates automatically when the source cell changes.
TEXTSPLIT is available in Excel for Microsoft 365 and Excel 2024. In Google Sheets the same job is done by the SPLIT function — the differences are covered below.
| Function category | Text functions |
| Excel versions | Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel for the web |
| Volatility | Non-volatile |
| Similar functions | LEFT, MID, RIGHT, FIND, SUBSTITUTE, REGEXEXTRACT |
TEXTSPLIT syntax
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with])
- text — the cell or string to split;
- col_delimiter — the character or text that marks where to spill the text across columns. Several delimiters go into an array constant: {",",";"};
- row_delimiter — optional: where to spill the text down rows. If you only need rows, skip the second argument: TEXTSPLIT(A2,,",");
- ignore_empty — optional: TRUE skips empty parts between consecutive delimiters; FALSE (default) keeps an empty cell for each;
- match_mode — optional: 0 (default) matches the delimiter case-sensitively, 1 ignores case;
- pad_with — optional: the value used to fill gaps when the rows of the result have different lengths. The default is #N/A.
The result is a dynamic array: you enter the formula in one cell and Excel spills the values right and down. The cells it spills into must be empty, otherwise you get a #SPILL! error.
Split a full name into first, middle and last name
The most common task: split names into separate cells. The delimiter is a space. The formula in B2 spills into three columns; fill it down and each row is split separately.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | First | Middle | Last |
| 2 | John Michael Smith | John=TEXTSPLIT(A2," ") | Michael | Smith |
| 3 | Mary Ann Lee | Mary=TEXTSPLIT(A3," ") | Ann | Lee |
| 4 | Tom Brown | Tom=TEXTSPLIT(A4," ") | Brown |
“Tom Brown” has only two parts, so D4 stays empty — and the last name lands in the “Middle” column. For names with and without a middle name, take the last word with =TEXTAFTER(A2," ",-1) instead. Double spaces create an extra empty cell: clean them with TRIM or set the fourth argument to TRUE.
To get just one part, wrap TEXTSPLIT in INDEX: =INDEX(TEXTSPLIT(A2," "),2) returns the second word.
Split cell text into rows
To send the parts down instead of right, pass the delimiter as the third argument and leave the second one empty. A cell with a list becomes a column.
| A | B | C | |
|---|---|---|---|
| 1 | Text | Result | |
| 2 | carrot, onion, potato | carrot=TEXTSPLIT(A2,,", ") | |
| 3 | onion | ||
| 4 | potato |
The two commas in a row in the formula are the skipped column delimiter. The delimiter “, ” includes the space, so the results don’t start with a space.
With both delimiters, TEXTSPLIT builds a whole table out of one cell: the row delimiter cuts the text into rows, and the column delimiter splits each row into columns.
| A | B | C | |
|---|---|---|---|
| 1 | Text | City | Sales |
| 2 | London=120;Paris=85;Rome=40 | London=TEXTSPLIT(A2,"=",";") | 120 |
| 3 | Paris | 85 | |
| 4 | Rome | 40 |
The text is first split by “;” into three rows, then each row by “=” into two columns. The numbers in the result are text; see below how to turn them into numbers.
This splits one cell at a time. To split a whole table into rows so that each value keeps its category next to it, see how to split a cell into rows.
Several delimiters, empty values and case
List several delimiters in an array constant separated by commas. The fourth argument decides what to do with consecutive delimiters, the fifth one whether the delimiter is case-sensitive.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Text | Formula | Result | |||
| 2 | apple, pear; plum | =TEXTSPLIT(A2,{", ","; "}) | apple=TEXTSPLIT(A2,{", ","; "}) | pear | plum | |
| 3 | a,,b,c | =TEXTSPLIT(A3,",") | a=TEXTSPLIT(A3,",") | b | c | |
| 4 | a,,b,c | =TEXTSPLIT(A4,",",,TRUE) | a=TEXTSPLIT(A4,",",,TRUE) | b | c | |
| 5 | 10x20X30 | =TEXTSPLIT(A5,"x") | 10=TEXTSPLIT(A5,"x") | 20X30 | ||
| 6 | 10x20X30 | =TEXTSPLIT(A6,"x",,,1) | 10=TEXTSPLIT(A6,"x",,,1) | 20 | 30 |
Row 2: two different delimiters at once. Rows 3–4: without TRUE, “,,” leaves an empty cell; with TRUE it disappears. Rows 5–6: by default “x” and “X” are different characters; with match mode 1 case doesn’t matter.
#N/A in the result and the pad_with argument
When the rows have different lengths, Excel fills the short ones with #N/A. The sixth argument replaces them with any value — an empty string, zero or a dash. Alternatively, wrap the formula in IFNA: =IFNA(TEXTSPLIT(A2,"=",";"),"").
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Text | Default | With pad_with | ||
| 2 | a=1;b=2;c | a=TEXTSPLIT(A2,"=",";") | 1 | a=TEXTSPLIT(A2,"=",";",,,"—") | 1 |
| 3 | b | 2 | b | 2 | |
| 4 | c | #N/A | c | — |
“c” has no second part, so C4 shows #N/A. The second formula puts a dash there.
Numbers after splitting are text
TEXTSPLIT always returns text, even when the parts are digits, and SUM skips text. A double minus in front of the function turns the whole array into numbers: =--TEXTSPLIT(A2,";"). More on this in the article about numbers stored as text.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Text | Parts (text) | Sum | ||
| 2 | 10;20;30 | 10=TEXTSPLIT(A2,";") | 20 | 30 | 60=SUM(--TEXTSPLIT(A2,";")) |
B2:D2 are left-aligned — they are text. The double minus inside SUM converts them, so the sum works without helper columns.
Split a whole column with one formula
Give TEXTSPLIT a range instead of a single cell and it returns only the first part of each row: Excel can’t hold an “array of arrays”. Two ways around it: fill a single-cell formula down, or first join the range into one string with TEXTJOIN and split it back into rows and columns at once.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | First part only | Whole table | ||
| 2 | John Smith | John=TEXTSPLIT(A2:A4," ") | John=TEXTSPLIT(TEXTJOIN(";",TRUE,A2:A4)," ",";") | Smith | |
| 3 | Mary Lee | Mary | Mary | Lee | |
| 4 | Tom Brown | Tom | Tom | Brown |
Column B shows only first names: that is how TEXTSPLIT treats a range. In D2 the rows are joined with “;” and split again: “;” into rows, the space into columns. TEXTJOIN is limited to 32,767 characters in the result.
If your Excel has no TEXTSPLIT
Excel 2021 and earlier don’t have the function — the formula returns #NAME?. Alternatives:
- the Text to Columns wizard on the Data tab splits a column by a delimiter in a few clicks, but the result doesn’t update;
- Flash Fill (Ctrl+E) guesses the pattern from one or two examples;
- a formula with classic functions: every space is replaced with 100 spaces, and MID cuts out the N-th 100-character chunk. The number 1, 2, 3 in the formula is the part number.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | Part 1 | Part 2 | Part 3 |
| 2 | John Michael Smith | John=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),(1-1)*100+1,100)) | Michael=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),(2-1)*100+1,100)) | Smith=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),(3-1)*100+1,100)) |
| 3 | Tom Brown | Tom=TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",100)),(1-1)*100+1,100)) | Brown=TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",100)),(2-1)*100+1,100)) | =TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",100)),(3-1)*100+1,100)) |
REPT stretches the text so that each word sits in its own 100-character window, and TRIM removes the padding. Replace the part number before “-1” with COLUMN(A1) to fill the formula to the right.
TEXTSPLIT vs Text to Columns vs Google Sheets SPLIT
| TEXTSPLIT | Text to Columns | SPLIT (Google Sheets) | |
|---|---|---|---|
| Updates when data changes | yes | no, one-off operation | yes |
| Splits into rows | yes, third argument | no | no, columns only |
| Several delimiters | array {",",";"} | checkboxes in the wizard | each character of the delimiter string is a separate delimiter |
| Empty values | kept unless ignore_empty is TRUE | “Treat consecutive delimiters as one” | removed by default |
| Versions | Microsoft 365, Excel 2024 | all Excel versions | Google Sheets |
A Google Sheets formula =SPLIT(A2," ") doesn’t become TEXTSPLIT when you open the file in Excel — SPLIT doesn’t exist in Excel, so replace it manually. Mind the difference: by default SPLIT treats each character of the delimiter as a separate delimiter, so SPLIT(A2,", ") splits both at commas and at spaces, while TEXTSPLIT(A2,", ") looks only for “comma + space”.
For a one-off split of a large table, Text to Columns is more convenient. To unpivot a whole table with categories into rows, !SEMTools has the “Unpivot and split 2nd column” procedure, and to move table rows onto separate sheets — “Split sheet by column value”.
Why TEXTSPLIT doesn’t work: common mistakes
- #NAME?. Your Excel version doesn’t have the function — it appeared in Microsoft 365 and Excel 2024. Use Text to Columns or the MID formula above.
- #SPILL!. The result has nowhere to spill: there are filled cells to the right or below. Clear them or move the formula.
- Extra empty cells. The text has double spaces or consecutive delimiters — set ignore_empty to TRUE or clean the text with TRIM.
- A space at the start of each part. The delimiter is missing its space: for “a, b, c” use ", ", not ",".
- The sum of the parts is zero. The parts are text; put a double minus before the function.
- Only the first word when referring to a range. TEXTSPLIT splits one cell; for a column, fill the formula down or use the TEXTJOIN trick.
TEXTSPLIT function: FAQ
What does the TEXTSPLIT function do in Excel?
It splits text by a delimiter and spills the parts across columns or rows: =TEXTSPLIT(A2," ") splits text at spaces.
Which Excel versions have TEXTSPLIT?
Excel for Microsoft 365 (Windows and Mac), Excel 2024 and Excel for the web. Excel 2021 and earlier don’t have it.
How do I split a full name into first and last name with a formula?
=TEXTSPLIT(A2," ") spills each part of the name into its own cell.
How do I split cell text into rows?
Pass the delimiter as the third argument: =TEXTSPLIT(A2,,", ").
Is there a SPLIT function in Excel?
No, SPLIT is a Google Sheets function. Its Excel equivalent is TEXTSPLIT.
Unpivot a table with categories into rows, split a sheet into several sheets or separate text and numbers in cells — without formulas, in a couple of clicks with !SEMTools, in any Excel version.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary: the TEXTSPLIT function in Excel
| Task | Formula |
|---|---|
| Split at spaces into columns | =TEXTSPLIT(A2," ") |
| Split into rows | =TEXTSPLIT(A2,,", ") |
| Into a table | =TEXTSPLIT(A2,"=",";") |
| Several delimiters | =TEXTSPLIT(A2,{", ","; "}) |
| No empty cells | =TEXTSPLIT(A2,",",,TRUE) |
| N-th part | =INDEX(TEXTSPLIT(A2," "),2) |
| Parts as numbers | =--TEXTSPLIT(A2,";") |
I hope you found what you were looking for on this page. TEXTSPLIT is the shortest way to split a cell with a formula; for one-off work there is Text to Columns, and related tasks are covered in the articles on how to separate text and numbers and get the first words from cells.