TEXTSPLIT Generator
Split one cell of text into separate columns using a delimiter you choose.
TEXTSPLIT
Split one cell of text into separate columns using a delimiter you choose.
The cell holding the combined text you want to break apart.
The character or text that separates the pieces, in quotes. Use " " for spaces or "|" for pipes.
=TEXTSPLIT(A2, ",")Worked Example
A2 contains Ada,Lovelace,London and you want each part in its own column.
=TEXTSPLIT(A2, ",")Returns: Spills three cells across the row: Ada, Lovelace, and London.
Checks Before You Paste
- •TEXTSPLIT spills its results across the cells to the right, so anything already sitting there causes a #SPILL! error. Clear the neighbouring cells rather than trying to fix the formula.
- •Values often keep a leading space when the source used a comma and a space. Either set the delimiter to ", " or wrap the whole thing as =TRIM(TEXTSPLIT(A2, ",")), which trims every spilled value at once.
- •Split on more than one delimiter by passing an array constant, for example =TEXTSPLIT(A2, {",",";"}). The full signature is TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]), and text delimiters are matched case sensitively unless you set the fifth argument, match_mode, to 1.
How TEXTSPLIT works
Availability: TEXTSPLIT requires Excel for Microsoft 365 or Excel for the web. It is not in Excel 2021, 2019, 2016 or any perpetual-licence release, and it is not in Google Sheets - Sheets has SPLIT(text, delimiter, [split_by_each], [remove_empty_text]), which is similar but not identical. A workbook using TEXTSPLIT opened in an older Excel shows the formula as _xlfn.TEXTSPLIT and returns #NAME?.
TEXTSPLIT breaks one string into several cells at a delimiter you choose. It is a dynamic-array function, so it does not need to be entered across a pre-selected range: write it in one cell and the results spill outward into as many cells as the split produces. That is the practical difference between TEXTSPLIT and the Text to Columns wizard - the formula stays live, so when the source cell changes the split changes with it, whereas Text to Columns is a one-time paste.
The second argument splits across columns and the third splits down rows, and you can use both at once. Given a string like Q1:120;Q2:135;Q3:150, splitting on ":" for columns and ";" for rows produces a three-row, two-column block from a single cell. Either delimiter can be an array constant such as {",",";"} when the source is inconsistent about which separator it used.
Two optional arguments handle messy input. ignore_empty, the fourth, defaults to FALSE, which means two delimiters in a row produce an empty cell - correct behaviour for a CSV row where a field is genuinely blank, and a nuisance when the source just has stray separators. pad_with, the sixth, fills the gaps when rows split into different numbers of columns; without it the short rows are padded with #N/A, which looks like a failure but is the documented default.
Everything TEXTSPLIT returns is text. Split "120,135,150" and the three results left-align and will not sum. Coerce them with =--TEXTSPLIT(A2, ",") or =VALUE(TEXTSPLIT(A2, ",")), both of which apply across the whole spilled array. If the pieces should be dates, wrap in DATEVALUE and format the result cells accordingly.
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])| Argument | Required | What it does |
|---|---|---|
text | Required | The string to split - a cell reference, quoted text, or another formula's result. A range is accepted in Microsoft 365 but splits only the first cell in most builds, so point it at a single cell and fill the formula down. |
col_delimiter | Required | The text that marks a split across columns, in quotes. May be more than one character (", ") or an array constant of several alternatives ({",",";"}). It can be omitted only if row_delimiter is supplied, in which case leave the position empty with a comma. |
row_delimiter | Optional | The text that marks a split down rows. Omit it and everything spills across a single row. Supply both delimiters to turn one cell into a two-dimensional block. |
ignore_empty | Optional | Default FALSE. FALSE keeps an empty cell for each pair of consecutive delimiters; TRUE collapses them so only non-empty pieces are returned. |
match_mode | Optional | Default 0, which matches the delimiter case sensitively. Set to 1 for a case-insensitive match. Only relevant when the delimiter contains letters, such as splitting on "and" or "X". |
pad_with | Optional | Default #N/A. The value used to fill positions in a ragged result where one row splits into fewer pieces than another. Pass "" for blank-looking padding. |
More worked examples
A2 holds the semicolon-separated address 45 Mill Lane;Bristol;BS1 5TR and each part needs its own column.
=TEXTSPLIT(A2, ";")Returns: Three cells across the row: 45 Mill Lane, Bristol, BS1 5TR.
The formula lives only in the first cell; the other two are spilled and cannot be edited directly. Selecting one shows the formula greyed out.
A2 holds a compact quarter summary, Q1:120;Q2:135;Q3:150, and you want a proper two-column table from it.
=TEXTSPLIT(A2, ":", ";")Returns: A three-row, two-column block: Q1 and 120, Q2 and 135, Q3 and 150.
The colon splits across columns and the semicolon down rows. The numbers arrive as text, so wrap the block in VALUE, or split the label and value columns separately if only one needs coercing.
A2 holds 10,,20,,30 - an export where the empty fields are real but not wanted in the result.
=TEXTSPLIT(A2, ",", , TRUE)Returns: Three cells: 10, 20, 30.
Without the TRUE the same formula spills five cells, two of which are empty. Note the doubled comma in the formula - the third argument, row_delimiter, is skipped rather than removed.
Common mistakes
- #SPILL! because something is already in the way
- TEXTSPLIT needs every cell it wants to write to be empty. A single value in the neighbouring cell blocks the whole result and the formula shows #SPILL!. Clicking the error triangle offers Select Obstructing Cells. Merged cells in the spill area cause the same error and cannot be worked around - unmerge them.
- Values arrive with a leading space
- Splitting "Ada, Lovelace, London" on "," gives " Lovelace" and " London" with the space still attached, which then breaks any exact-match lookup against them. Either set the delimiter to ", " so the space is consumed, or wrap the whole call as =TRIM(TEXTSPLIT(A2, ",")), which trims every spilled value at once.
- #N/A padding on ragged rows
- When one source string splits into four pieces and another into three, the shorter result is padded with #N/A. This is the documented default, not an error in your formula. Supply the sixth argument to choose something else: =TEXTSPLIT(A2, ",", ";", , , "").
- Case-sensitive delimiters by default
- =TEXTSPLIT(A2, "x") will not split "12X15", because match_mode defaults to 0. Pass 1 as the fifth argument for a case-insensitive match. This bites most often when splitting on words like "and" or on unit letters.
- The split results are text and will not calculate
- =SUM(TEXTSPLIT(A2, ",")) on "10,20,30" returns 0, because all three pieces are text. Coerce with a double unary - =SUM(--TEXTSPLIT(A2, ",")) returns 60 - or wrap in VALUE. Currency symbols and thousands separators in the source need stripping with SUBSTITUTE before either will work.
Frequently Asked Questions
Their Excel does not have the function. TEXTSPLIT shipped with Microsoft 365 only, so Excel 2021 and earlier cannot evaluate it and store it as _xlfn.TEXTSPLIT, which surfaces as #NAME?. The values you can see on your screen are still in the file, but they will recalculate to errors the moment the sheet refreshes. For a shared workbook, split the data once and paste the results as values.
Put the delimiter in the third argument and leave the second empty: =TEXTSPLIT(A2, , ";") spills the pieces down a column. Supplying both the second and third arguments splits in both directions at once, producing a grid from a single cell.
Pass TRUE as the fourth argument, ignore_empty: =TEXTSPLIT(A2, ",", , TRUE). The default of FALSE preserves an empty cell for each pair of consecutive delimiters, which is what you want for a CSV row with genuinely missing fields and what you do not want for a string with stray separators.
Three options, in ascending order of effort. Data > Text to Columns splits a selected column in place and is by far the quickest, but the result is static. Power Query's Split Column step refreshes with the data. For a live formula there is the FILTERXML trick, =TRANSPOSE(FILTERXML("<t><s>" & SUBSTITUTE(A2, ",", "</s><s>") & "</s></t>", "//s")), which works on Windows Excel 2013 and later but not on Excel for Mac.
Yes, pass an array constant in place of a single delimiter: =TEXTSPLIT(A2, {",",";"}) splits on either character. The same works for the row delimiter. Combine it with ignore_empty set to TRUE, because mixed-delimiter data usually has runs of separators that would otherwise produce blanks.
Related Tools
Text to Columns
Split an uploaded column by delimiter without needing Microsoft 365.
LEFT RIGHT MID Generator
Extract one piece by position when a full split is more than you need.
TEXTJOIN Generator
Go the other way and collapse several cells back into one delimited string.
SUBSTITUTE Generator
Normalise mixed separators to a single character before splitting.