SPLIT Function in Google Sheets: Parse Text Like a Pro

Q: Can SPLIT handle multiple delimiters at once?

Yes. By default, the split_by_each parameter is TRUE, so each character in your delimiter argument acts as an independent split point. Set split_by_each to FALSE if you want to match an exact multi-character delimiter string.

Q: Why does my SPLIT formula return a #REF! error?

A #REF! error usually means the split results would extend beyond the sheet boundary or overwrite another formula or merged cell. Ensure there is enough empty space to the right of your formula cell.

Key Takeaways:The SPLIT function breaks text into separate cells using any delimiter you chooseCombine SPLIT with INDEX to extract exactly the piece of text you needUse ARRAYFORMULA + SPLIT to parse an entire column of data in one formulaWhen importing large CSV files with mixed delimiters, SmoothSheet handles parsing automatically

You have a column of full names and you need first names in one column, last names in another. Or maybe you have email addresses and you need just the domain. Perhaps you exported data where multiple values are crammed into a single cell, separated by commas. The SPLIT function in Google Sheets solves all of these problems by breaking text apart at any delimiter you specify.

Unlike manual copy-paste or find-and-replace workarounds, SPLIT dynamically parses text into adjacent cells. Change the source data, and your split results update automatically. In this guide, you will learn the SPLIT syntax, walk through practical examples, and discover advanced techniques like combining SPLIT with INDEX, ARRAYFORMULA, and TEXTJOIN.

SPLIT Function Syntax

The SPLIT function in Google Sheets uses this syntax:

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

Here is what each parameter does:

text (required) — The string or cell reference you want to split.
delimiter (required) — The character(s) where the text should be divided. Can be a single character like a comma, space, or pipe, or multiple characters.
split_by_each (optional, default TRUE) — When TRUE, each character in the delimiter string is treated as a separate delimiter. When FALSE, the entire delimiter string must appear as-is to trigger a split.
remove_empty (optional, default TRUE) — When TRUE, empty text results from consecutive delimiters are removed. When FALSE, empty cells are preserved.

The split_by_each parameter is the one that trips up most users. If your delimiter is ", " (comma-space), setting it to TRUE would split on every comma AND every space individually. Set it to FALSE to split only on the exact string ", ". More on this in the examples below.

Basic SPLIT Examples

Split by Comma

The most common use case is splitting comma-separated values. Suppose cell A1 contains Marketing,Sales,Engineering:

=SPLIT(A1, ",")

This returns three values in adjacent cells: Marketing | Sales | Engineering.

If your data uses ", " (comma followed by a space) as the separator, the default split_by_each=TRUE still works since it splits on commas and spaces individually. But for cleaner control, you can use:

=SPLIT(A1, ", ", FALSE)

This treats the entire ", " as one delimiter, which is more predictable when your values themselves might contain spaces.

Working with CSV data that uses different delimiters? SmoothSheet's free CSV Delimiter Converter lets you switch between comma, semicolon, tab, and pipe formats before importing into Sheets.

Split by Space (First Name / Last Name)

Splitting full names into first and last names is one of the most frequent real-world uses. If cell A1 contains Jane Smith:

=SPLIT(A1, " ")

Result: Jane | Smith in two adjacent cells.

What about names with three parts, like Mary Jane Watson? SPLIT returns all three parts in separate cells. If you only want the first name and everything else as the last name, you will need to combine SPLIT with other functions, which we cover in the advanced section below.

Split by Multiple Delimiters

This is where the split_by_each parameter shines. Suppose you have messy data like red;blue,green|yellow and you want to split on semicolons, commas, and pipes all at once:

=SPLIT(A1, ";,|")

Because split_by_each defaults to TRUE, Google Sheets treats each character in ";,|" as its own delimiter. The result: red | blue | green | yellow.

Compare this with:

=SPLIT(A1, ";,|", FALSE)

Here, Google Sheets would look for the exact string ";,|" in the text, which does not exist, so nothing gets split. Understanding this distinction saves you from unexpected results.

Advanced SPLIT Techniques

SPLIT + INDEX to Extract Specific Parts

SPLIT always returns results across multiple cells. But what if you only need the second value? Wrap SPLIT with INDEX:

=INDEX(SPLIT(A1, ","), 1, 2)

This extracts only the second element from the split result. The 1 refers to the first (and only) row, and 2 refers to the second column.

A practical example: extracting the domain from an email address like [email protected]:

=INDEX(SPLIT(A1, "@"), 1, 2)

Result: example.com.

You can also grab the last element dynamically. If you do not know how many parts the split will produce:

=INDEX(SPLIT(A1, "/"), 1, COUNTA(SPLIT(A1, "/")))

This counts the number of split results with COUNTA and uses that as the column index, always returning the last piece.

SPLIT with ARRAYFORMULA for Entire Columns

SPLIT does not natively work inside ARRAYFORMULA the way some other functions do, because SPLIT itself returns an array (multiple columns). However, you can combine them for specific extraction tasks.

For example, to extract the first name from a full column of names in A2:A100:

=ARRAYFORMULA(IF(A2:A100="", "", INDEX(SPLIT(A2:A100, " "), , 1)))

The IF wrapper prevents errors on empty rows. The INDEX(..., , 1) grabs the first column from each split result.

For extracting the second part (like last names), replace the column index:

=ARRAYFORMULA(IF(A2:A100="", "", INDEX(SPLIT(A2:A100, " "), , 2)))

Note: this approach works well when every row has the same number of parts after splitting. If some rows have two words and others have three, you may get unexpected results or errors on rows with fewer parts.

Reverse SPLIT with TEXTJOIN

Sometimes you need to do the opposite of SPLIT: combine values from multiple cells back into one. That is where TEXTJOIN comes in:

=TEXTJOIN(", ", TRUE, B1:D1)

This joins the values in B1 through D1 with a comma and space, skipping any empty cells when the second argument is TRUE.

A common workflow: SPLIT a value to clean or transform individual parts, then TEXTJOIN them back together. For example, reversing a name from Smith, Jane to Jane Smith:

=TEXTJOIN(" ", TRUE, INDEX(SPLIT(A1, ", "), 1, 2), INDEX(SPLIT(A1, ", "), 1, 1))

Real-World Use Cases

Beyond simple name splitting, the SPLIT function handles many practical data parsing tasks.

Parse Email Domains

To extract domains from a list of email addresses for reporting:

=INDEX(SPLIT(A1, "@"), 1, 2)

Apply this across a column to quickly see which email providers your contacts use. Combine with the QUERY function to count unique domains.

Extract URL Parameters

If you track campaign URLs like https://example.com/page?utm_source=google&utm_medium=cpc, you can extract the query string:

=INDEX(SPLIT(A1, "?"), 1, 2)

This returns utm_source=google&utm_medium=cpc. Then split again on & to isolate individual parameters:

=SPLIT(INDEX(SPLIT(A1, "?"), 1, 2), "&")

Nested SPLIT calls are perfectly valid and often the cleanest approach for multi-level parsing.

Split Addresses

Address data often arrives as a single string: 123 Main St, Austin, TX 78701. Splitting on ", " (comma-space) gives you the street, city, and state/zip:

=SPLIT(A1, ", ", FALSE)

Remember to set split_by_each to FALSE here. Otherwise, spaces within the street address would also trigger splits.

If you are importing large datasets of addresses from CSV files, SmoothSheet processes files server-side so your browser does not freeze, even with hundreds of thousands of rows. Once your data is in Google Sheets, SPLIT handles the parsing.

Common Errors and Fixes

Even a straightforward function like SPLIT has its gotchas. Here are the most common issues and how to resolve them.

#VALUE! Error

The most frequent SPLIT error occurs when the result would overwrite existing data in adjacent cells. SPLIT expands horizontally, so if the cells to the right already contain data, Google Sheets throws a #VALUE! error.

Fix: Clear the cells to the right of your SPLIT formula, or move your formula to a column with enough empty space. For a reference on how Google Sheets limits affect wide-expanding formulas, check the official SPLIT documentation.

Empty Results Between Delimiters

If your text has consecutive delimiters (like a,,b), SPLIT removes the empty results by default. If you need to preserve the empty cell to maintain column alignment:

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

Setting remove_empty to FALSE keeps empty cells in place. This is essential when column position matters, such as when parsing fixed-format CSV exports.

Delimiter Not Found

If the delimiter does not exist in the text, SPLIT returns the entire text as a single value with no error. This is actually useful because it means SPLIT formulas will not break on rows where the delimiter is missing. However, if you expect multiple columns and only get one, double-check your delimiter character. Common mistakes include using a regular space when the data contains non-breaking spaces, or mixing up semicolons and commas in international CSV formats.

SmoothSheet's free CSV Delimiter Detector tool can identify exactly which delimiter your file uses, taking the guesswork out of the equation.

Frequently Asked Questions

Can SPLIT handle multiple delimiters at once?

Yes. By default, the split_by_each parameter is TRUE, which means each character in your delimiter argument acts as an independent split point. For example, =SPLIT(A1, ",; ") splits on commas, semicolons, and spaces. Set split_by_each to FALSE if you want to match the exact multi-character delimiter string.

How do I split text into rows instead of columns?

SPLIT always outputs horizontally across columns. To convert the result into rows, wrap it with TRANSPOSE: =TRANSPOSE(SPLIT(A1, ",")). This stacks the split values vertically in a single column, which is useful for creating dropdown lists or vertical data layouts.

Why does my SPLIT formula return a #REF! error?

A #REF! error usually means the split results would extend beyond the sheet boundary (past column Z or beyond the last column). This can happen when splitting text with many delimiters. It can also appear if the formula output would overwrite another formula or merged cell. Ensure there is enough empty space to the right of your formula cell.

What is the difference between SPLIT and LEFT/RIGHT/MID?

LEFT, RIGHT, and MID extract text based on character position (you need to know exactly how many characters to grab). SPLIT divides text based on a delimiter character, regardless of position or length. SPLIT is usually the better choice when your data has a consistent separator but variable-length values, which is the case with most real-world data like names, emails, and CSV fields.

Wrapping Up

The SPLIT function is one of the most practical text-parsing tools in Google Sheets. Whether you are separating names, extracting email domains, or pulling apart URL parameters, it handles the heavy lifting with a clean, readable formula. Pair it with INDEX for precision extraction, ARRAYFORMULA for bulk processing, or TEXTJOIN to recombine values after transformation.

For day-to-day spreadsheet tasks, SPLIT and its companion functions cover most text-parsing needs. But when you are dealing with large CSV or Excel files that need to get into Google Sheets in the first place, SmoothSheet handles the import without browser crashes. At $9/month, it processes files server-side so you can focus on the analysis, not the upload.