TEXTSPLIT function

TEXTSPLIT breaks a text string into a dynamic array by column and optional row delimiters, returning each piece in its own cell.

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Generate a TEXTSPLIT formula

Describe what you need. The generator will reach for TEXTSPLIT where TEXTSPLIT is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How TEXTSPLIT reads its arguments
textrequiredcol_delimiterrequiredrow_delimiteroptionalignore_emptyoptionalmatch_modeoptionalpad_withoptionalTEXTSPLIT →
ArgumentRequiredDescription
textRequiredString (or reference to a cell containing text) that you want to split; if omitted the function returns #VALUE!.
col_delimiterRequiredString (or array of strings) that marks where each column ends; cannot be an empty string or the function throws #VALUE!.
row_delimiterOptionalOptional string that separates rows; if omitted the text is treated as a single row, and extra delimiters create extra columns only.
ignore_emptyOptionalOptional logical flag – TRUE removes empty results from the spill, FALSE (default) keeps them as blank cells; non-boolean values cause #VALUE!.
match_modeOptionalOptional 0 (exact) or 1 (wildcard) that controls whether delimiters are treated as literals or patterns; any other number returns #VALUE!.
pad_withOptionalOptional value used to fill missing cells when rows have unequal column counts; if the shape does not match the spill area, #NUM! is returned.

Returns

It returns a spilled array of strings sized by the number of rows and columns produced.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Split a full budget table into a grid

Category,Month,Budgeted,Actual,Variance|Food,Jan,200,180,-20|Utilities,Jan,150,160,10|Rent,Jan,800,800,0
=TEXTSPLIT(A1,",","|")

Result: A 4-row × 5-column spilled array: Category Month Budgeted Actual Variance Food Jan 200 180 -20 Utilities Jan 150 160 10 Rent Jan 800 800 0

The formula reads the text in A1, uses a comma to cut each column and a pipe to cut each row. Because there are four pipe-separated sections, TEXTSPLIT creates four rows, each with five comma-separated fields, and spills the result onto the worksheet.

2. Extract only the Budgeted column

Category,Month,Budgeted,Actual,Variance|Food,Jan,200,180,-20|Utilities,Jan,150,160,10|Rent,Jan,800,800,0
=INDEX(TEXTSPLIT(A1,",","|"),,3)

Result: A vertical array: Budgeted 200 150 800

First TEXTSPLIT creates the full 4 × 5 grid as in the previous example. Wrapping it in INDEX with a blank row argument and column 3 tells Excel to return only the third column of that spill, which holds the budgeted amounts for each line item.

3. Remove blank cells from an irregular budget string

Category,Month,Budgeted,Actual,Variance|Food,Jan,200,180,|Utilities,Jan,150,,10|Rent,Jan,800,800,
=TEXTSPLIT(A2,",","|",TRUE)

Result: A 4-row × 4-column array (blanks omitted): Category Month Budgeted Actual Food Jan 200 180 Utilities Jan 150 10 Rent Jan 800 800

The text in A2 contains empty fields after some commas. By setting ignore_empty to TRUE, TEXTSPLIT drops any empty strings that would otherwise appear as blank cells, yielding a compact array that only includes cells with actual content.

Common errors

Which TEXTSPLIT error are you seeing?
TEXTSPLIT returned an error#SPILL!
Clear the cells where the array would spill or move the formula to a location with enough empty space.
#VALUE!
Provide a non-empty delimiter such as "," or "|"; if you need to split on nothing, consider using REPT or other functions instead.
#NUM!
Either omit pad_with or supply a single scalar value (e.g., "N/A") that can be broadcast to all missing cells.
ErrorWhy it happensHow to fix it
#SPILL!The formula tries to spill its array into a range that already contains data, so Excel cannot place the result.Clear the cells where the array would spill or move the formula to a location with enough empty space.
#VALUE!A delimiter argument is an empty string, which TEXTSPLIT does not accept because it cannot determine split points.Provide a non-empty delimiter such as "," or "|"; if you need to split on nothing, consider using REPT or other functions instead.
#NUM!The pad_with argument is supplied but its dimensions do not match the uneven row lengths produced by the split, causing a size mismatch.Either omit pad_with or supply a single scalar value (e.g., "N/A") that can be broadcast to all missing cells.

Tips and when to use something else

  • Use a single-character delimiter for fastest performance; multi-character delimiters work but are slower.
  • When you only need to split on one delimiter and ignore row breaks, you can set row_delimiter to an empty string and let TEXTSPLIT treat the whole string as one row.
  • If you need to split text that contains delimiters inside quoted strings, preprocess with SUBSTITUTE or REGEXREPLACE before calling TEXTSPLIT.
  • For simple one-column extraction, consider using the older SPLIT function in Google Sheets (which is not available in Excel) or FILTER combined with SEARCH instead of TEXTSPLIT.

Related functions

TEXTJOINTEXTJOIN merges multiple text strings or ranges into one string, using a chosen delimiter and optional ignoring of empty cells.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.CHOOSECOLSCHOOSECOLS returns the selected columns from a given array, preserving order and data types, and spills the result into adjacent cells.LETLET lets you name a single intermediate result inside a formula, then return that result, making complex calculations clearer and easier to read.

Frequently asked questions

Can TEXTSPLIT split on multiple different delimiters at once?
Yes. You can pass an array of delimiters to col_delimiter or row_delimiter, such as {",",";"}. Each element in the array is treated as an alternative delimiter, and TEXTSPLIT will split on any of them.
Why does TEXTSPLIT return a #SPILL! error even though the surrounding cells are empty?
Spill errors also occur when a dynamic array would intersect a merged cell, a table, or a protected range. Check for hidden objects or formatting that might block the spill area and adjust accordingly.
How do I keep leading or trailing spaces from being trimmed when using TEXTSPLIT?
TEXTSPLIT does not automatically trim spaces; any spaces that appear in the source text remain in the result. If you see trimmed values, they are likely caused by subsequent functions like TRIM applied to the spill.
Is there a way to split a text string without creating a spill, returning a single cell instead?
Wrap TEXTSPLIT inside the CONCAT or TEXTJOIN function to concatenate the array back into a single string, or use INDEX to pull a single element from the spilled result.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator