Does SPLIT always return data horizontally, or can it go vertical?

SPLIT returns results horizontally across columns by default. To orient vertically down rows, wrap it with TRANSPOSE: =TRANSPOSE(SPLIT(...)).

What happens when the delimiter appears twice in a row?

Consecutive delimiters create empty cells in the result. Set remove_empty_text to TRUE to automatically skip these blanks, or post-process with FILTER to remove them.

Can I split by multiple different delimiters at once?

SPLIT handles one delimiter at a time. For multiple delimiters, use REGEXSPLIT with a regex pattern, e.g., REGEXSPLIT(text, "[,;]") to split by comma or semicolon.

Can I use SPLIT inside other formulas like VLOOKUP or nested IFs?

Yes, SPLIT returns an array that most functions accept. Nest it in IF, IFS, FILTER, or other array-aware functions, but remember the output spans multiple cells.

SPLIT function

SPLIT breaks a text string into an array of substrings by dividing on a specified delimiter or by individual characters.

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

Generate a SPLIT formula

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

Arguments

How SPLIT reads its arguments

Argument	Required	Description
text	Required	The string to split; if empty, NULL, or omitted, returns #N/A error.
delimiter	Required	The character or substring to split on; must be text; if omitted or empty, returns #N/A; if not found in text, returns the entire string as one value.
split_by_each	Optional	Optional. If TRUE, splits text into individual characters and ignores the delimiter argument; defaults to FALSE.
remove_empty_text	Optional	Optional. If TRUE, removes empty cells from the result; defaults to FALSE, which preserves blanks where consecutive delimiters appear.

Returns

An array of text values, displayed horizontally across columns or vertically down rows based on structure.

Availability

Excel: Not available · Google Sheets: Supported

Worked examples

1. Extract individual quiz scores from concatenated data

Student	Subject	Assignment	Scores	Max
Alice	Math	Quiz 1, Quiz 2, Quiz 3	85,92,88	100

=SPLIT(D2, ",")

Result: [85, 92, 88]

SPLIT breaks the comma-separated scores in D2 into three columns. Each score becomes a separate cell, enabling you to reference, sort, or average individual quiz results without manual column setup.

2. Clean assignment names with stray delimiters from imports

Student	Subject	Assignment	Scores	Max
Bob	English	Essay,,Final,Reflection	92,88,95	100

=SPLIT(C3, ",", FALSE, TRUE)

Result: [Essay, Final, Reflection]

With remove_empty_text set to TRUE, SPLIT discards the blank cell created by the double comma between 'Essay' and 'Final'. This cleanup prevents misaligned columns and downstream formula errors that occur when empty cells shift data unexpectedly.

3. Split character codes into separate gradebook columns

Student	Subject	Assignment	Code	Max
Charlie	Science	Lab Report	M_Q1_H	100

=SPLIT("M_Q1_H", "_")

Result: [M, Q1, H]

The assignment code 'M_Q1_H' (where M=mandatory, Q1=quarter 1, H=honors section) is split into three meaningful parts. This structured extraction allows filtering by quarter, difficulty level, or requirement status without parsing the concatenated identifier manually.

Common errors

Which SPLIT error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The delimiter argument is omitted, is an empty string (""), or is a non-text data type like a number.	Provide a non-empty text delimiter: use "," instead of 0, or "," instead of an empty string "".
#SPILL!	The resulting array is too large to fit in available space, or overlaps with existing data in adjacent cells.	Clear cells to the right or below the formula, or move it to a location with sufficient empty space to hold the full result.
#N/A	The text argument is empty, blank, NULL, or the delimiter parameter is missing entirely.	Ensure both text and delimiter are provided as non-empty values, and verify that source cells are not blank.

Tips and when to use something else

Use SPLIT to break concatenated data (e.g., 'Quiz 1, Quiz 2' → separate columns) and JOIN to combine them back—they are complementary operations.
When split_by_each is TRUE, SPLIT ignores the delimiter and returns individual characters; leave it FALSE for typical delimiter-based splitting.
Always use remove_empty_text TRUE when importing messy data from copy-paste or external sources to prevent blank cells that break downstream formulas.
For complex splitting patterns (e.g., split by any punctuation), use REGEXSPLIT instead; SPLIT works best with simple, fixed delimiters.

Related functions

JOINConcatenates multiple text values or array elements into a single string, separated by a delimiter you specify.TEXTJOINTEXTJOIN merges multiple text strings or ranges into one string, using a chosen delimiter and optional ignoring of empty cells.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.TRANSPOSETRANSPOSE flips a row-or-column array, turning rows into columns and columns into rows, returning a spilled array of the same size swapped.

Frequently asked questions

Does SPLIT always return data horizontally, or can it go vertical?: SPLIT returns results horizontally across columns by default. To orient vertically down rows, wrap it with TRANSPOSE: =TRANSPOSE(SPLIT(...)).
What happens when the delimiter appears twice in a row?: Consecutive delimiters create empty cells in the result. Set remove_empty_text to TRUE to automatically skip these blanks, or post-process with FILTER to remove them.
Can I split by multiple different delimiters at once?: SPLIT handles one delimiter at a time. For multiple delimiters, use REGEXSPLIT with a regex pattern, e.g., REGEXSPLIT(text, "[,;]") to split by comma or semicolon.
Can I use SPLIT inside other formulas like VLOOKUP or nested IFs?: Yes, SPLIT returns an array that most functions accept. Nest it in IF, IFS, FILTER, or other array-aware functions, but remember the output spans multiple cells.

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

Reviewed 2026-09-17