SPLIT Function in Google Sheets: How to Use It

How-to basics · Updated November 22, 2026

The SPLIT function in Google Sheets divides text into separate cells based on a delimiter, such as a comma, space, or custom character. For example, =SPLIT("apple,banana,cherry", ",") places “apple” in one cell, “banana” in the next, and “cherry” in the third, automatically filling columns to the right.

What Is SPLIT and When to Use It

SPLIT is a text function unique to Google Sheets (Excel uses TEXTSPLIT instead). It takes a string or cell reference, splits it at every occurrence of a specified delimiter, and outputs the pieces into adjacent cells horizontally. This is ideal for cleaning imported data, parsing full names, or breaking comma-separated lists into usable columns.

Common use cases:

  • Splitting a full name into first and last name (delimiter: space).
  • Breaking a URL into parts (delimiter: “/”).
  • Separating tags or keywords (delimiter: comma or semicolon).
  • Parsing log entries or CSV-like data pasted into a single cell.

Syntax and Arguments Explained

The syntax is: =SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

  • text (required) – the string or cell reference to split.
  • delimiter (required) – the character or substring to split on. Enclose it in quotes, e.g., "," or " ".
  • split_by_each (optional) – TRUE (default) treats each character in the delimiter as a separate split point; FALSE treats the entire delimiter as one unit.
  • remove_empty_text (optional) – TRUE (default) removes empty cells; FALSE keeps them.

Example with options: =SPLIT("a1,b2,c3", ",", FALSE, TRUE) splits only on commas, not on the digits, returning “a1”, “b2”, “c3”.

Step-by-Step: Using SPLIT in Google Sheets

  1. Open your Google Sheets document in a web browser.
  2. Click on the cell where you want the first result to appear (e.g., B2).
  3. Type =SPLIT( and then click the cell containing the text (e.g., A2).
  4. Type a comma, then the delimiter in quotes, e.g., ",".
  5. Close the parenthesis and press Enter. The results will spill into the cells to the right automatically.

For example, if A2 contains “John Doe”, entering =SPLIT(A2, " ") in B2 will put “John” in B2 and “Doe” in C2.

SPLIT vs. Other Text Tools in Google Sheets

SPLIT is not the only way to separate text. Compare it with:

  • Text to Columns (Data > Split text to columns): A menu-driven tool that splits a column permanently without formulas. It uses a delimiter you choose (comma, space, custom). Unlike SPLIT, it overwrites the original data with the split results.
  • LEFT, RIGHT, MID: Extract specific portions by character position. For example, =LEFT(A2, 5) gets the first 5 characters. These are useful when the delimiter is not consistent.
  • REGEXEXTRACT: Pulls text matching a regular expression pattern, useful for complex patterns like emails or phone numbers.
  • Excel’s TEXTSPLIT: In Microsoft 365, TEXTSPLIT works similarly but uses a different syntax and can split into rows or columns.

Common Errors and How to Fix Them

SPLIT can return errors or unexpected results. Here are frequent issues:

  • #VALUE! error: Occurs if the delimiter is not found. Check that the delimiter exactly matches the text, including spaces.
  • #N/A error: Usually appears when you try to use SPLIT on a range of cells without ARRAYFORMULA. For example, =SPLIT(A2:A5, ",") will error. Use =ARRAYFORMULA(SPLIT(A2:A5, ",")) instead.
  • Text not splitting as expected: If the delimiter has multiple characters, set split_by_each to FALSE. For instance, =SPLIT("a--b--c", "--", FALSE) returns “a”, “b”, “c”.
  • Empty cells appearing: Set remove_empty_text to TRUE (default) to suppress them.

Advanced Tips: Combining SPLIT with Other Functions

SPLIT becomes powerful when nested with other functions:

  • With ARRAYFORMULA: Split multiple rows at once. Example: =ARRAYFORMULA(SPLIT(A2:A10, ",")) splits each cell in A2:A10.
  • With TRANSPOSE: Convert horizontal output to vertical. Example: =TRANSPOSE(SPLIT(A2, ",")) lists the parts in a column.
  • With INDEX: Extract a specific piece. Example: =INDEX(SPLIT(A2, " "), 1, 2) returns the second word.
  • With QUERY: Use SPLIT inside a query to parse data. For instance, =QUERY(SPLIT(A2:A5, ","), "select Col1 where Col2 = 'x'").

Frequently Asked Questions

Can SPLIT output into rows instead of columns? No, SPLIT always outputs horizontally to the right. To get vertical output, wrap it with TRANSPOSE: =TRANSPOSE(SPLIT(A2, ",")).

Why does my SPLIT formula show only the first word? This happens if the delimiter is not correct, or if there is an extra space. For example, splitting “John Doe” with “,” (comma) will fail. Use ” ” (space) or trim the text first: =SPLIT(TRIM(A2), " ").

Does SPLIT work on an entire column at once? Yes, but you need ARRAYFORMULA. For example, =ARRAYFORMULA(SPLIT(A2:A10, ",")) splits each cell in the range. Without ARRAYFORMULA, it will only process the first cell.

Is SPLIT available in Excel? No, SPLIT is a Google Sheets function. In Excel, use the TEXTSPLIT function (Microsoft 365) or the Text to Columns wizard (Data > Text to Columns).

How do I split by multiple delimiters like comma and semicolon? Set split_by_each to TRUE (default) and include both in the delimiter string: =SPLIT(A2, ",;") will split on either comma or semicolon. Be aware that it splits on each character individually.

Can I use SPLIT to split by a line break? Yes, use the CHAR function: =SPLIT(A2, CHAR(10)) splits on newline characters. This is useful for pasted multi-line text.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.