Google Sheets SPLIT Formula: How to Separate Text Cleanly

In Google Sheets, use =SPLIT(A2, ",") to divide the text in A2 at each comma, with the pieces spilling into neighboring columns. Replace the comma with the delimiter in your data; for an exact multi-character delimiter, use =SPLIT(A2, " - ", FALSE, TRUE).

What the SPLIT formula does

SPLIT takes one text value and separates it into multiple values wherever it finds a specified delimiter. A delimiter is the character or sequence of characters between the parts you want to separate. For example, a comma can divide a list of names, a tab can separate fields copied from another system, and a hyphen can divide a product code.

The basic syntax is SPLIT(text, delimiter, [split_by_each], [remove_empty_text]). The square brackets in this description indicate optional arguments; do not type them into the formula. In a sheet, the formula usually refers to a cell containing the original text, so you can fill it down to process other rows.

text is the cell or text you want to separate.
delimiter is the separator Sheets should look for.
split_by_each controls how Sheets treats a delimiter made of multiple characters.
remove_empty_text controls whether empty pieces between repeated delimiters are kept.

The last two arguments are optional. If you omit them, Sheets treats each character in the delimiter as a possible separator and removes empty pieces. That default is convenient for a single comma, but the details matter when your delimiter includes more than one character or your text contains repeated separators.

Basic example: split comma-separated values

Suppose A2 contains Jordan Lee, Portland, Oregon, and you want the three parts in separate columns. With the source text in A2 and commas separating the fields, enter =SPLIT(A2, ",") in B2. The result is Jordan Lee in B2, a space followed by Portland in C2, and a space followed by Oregon in D2. SPLIT separates at the commas; it does not automatically remove the spaces that follow them.

If you control the source format, it can be helpful to use a delimiter that does not include surrounding spaces and then clean the returned pieces. With the same text in A2, enter =ARRAYFORMULA(TRIM(SPLIT(A2, ","))) in B2. It returns Jordan Lee, Portland, and Oregon across B2:D2 without the extra leading spaces. TRIM also removes repeated ordinary spaces within a value, so avoid it if those spaces are meaningful.

For a short, fixed text value, you can also supply the text directly instead of referencing a cell: =SPLIT("red|green|blue", "|") returns red, green, and blue in adjacent cells. A cell reference is generally more useful for a working spreadsheet because the output updates when the source cell changes.

Choose the right delimiter

The formula can split at punctuation, spaces, or other characters as long as the delimiter in the formula matches the text. If A2 contains first.last@example.com and you want the username and domain, use a period as the delimiter. If A2 contains INV-2026-041 and you want its three code parts, use a hyphen. Check the data first: splitting at a character that also appears inside a field can create more pieces than intended.

Whitespace can be a delimiter too. With the sentence one two three in A2, =SPLIT(A2, " ") separates at ordinary spaces. This is useful for consistently formatted text, but it is not a dependable way to separate full names: a person may have a middle name, a compound surname, or an honorific. In those cases, the number of words does not reliably identify which word belongs in which field.

For a delimiter that is a sequence, such as the characters space, slash, and space in North / South, set the third argument to FALSE. With that full text in A2, use =SPLIT(A2, " / ", FALSE, TRUE). Setting split_by_each to FALSE tells Sheets to match the delimiter as a complete sequence. Without it, Sheets treats the space and slash as separate delimiters, which may split values in unintended places.

Understand the optional arguments

The third argument, split_by_each, is especially useful when the delimiter has multiple characters. Its default is TRUE, meaning each character in the delimiter can independently trigger a split. For a delimiter such as ::, that default will split at each colon. If you want to split only where the complete two-colon sequence occurs, set the argument to FALSE: with item::size::color in A2, =SPLIT(A2, "::", FALSE, TRUE) returns item, size, and color.

The fourth argument, remove_empty_text, determines what happens when delimiters are next to each other or appear at an edge. The default TRUE removes empty results. To keep them, set the fourth argument to FALSE. With red,,blue in A2, =SPLIT(A2, ",", TRUE, FALSE) preserves the empty field between the two commas, producing red, an empty value, and blue. This can matter when blank fields represent missing information in a structured record.

These arguments do not change the source cell. SPLIT creates a calculated result elsewhere in the sheet, so the original text remains available unless you later replace it yourself.

Put the results in rows or process many records

By default, SPLIT returns its pieces horizontally, across columns. Leave enough empty cells to the right of the formula for every expected part. To put the pieces vertically instead, wrap the function in TRANSPOSE. With red|green|blue in A2, enter =TRANSPOSE(SPLIT(A2, "|")) in B2; the three values spill down B2:B4.

For a list of records, place the formula beside the first record and copy it down. For example, if each row in A2:A20 contains one comma-separated record, put =SPLIT(A2, ",") in B2, then fill the formula down for the other rows. Each row’s output needs enough clear columns, and the source reference adjusts as the formula is copied.

After checking the results, you may want to keep them as fixed values rather than formulas. Copy the output cells, then use Edit > Paste special > Values only. This replaces formulas in the selected destination cells with their displayed results. Make a copy or confirm the selection first if you need to retain the original formulas.

Use the menu instead of a formula

For a one-time split, Google Sheets also has a menu tool: select the cells, then choose Data > Split text to columns. Sheets displays a separator control; choose a suggested separator or select Custom and provide one. The source text is divided in the sheet, so check that the destination columns are empty before applying the command. Unlike a formula, this operation is not a live calculation that automatically updates from the original text.

The menu can be faster when you want to divide a selected range once and do not need to preserve a formula. SPLIT is usually a better choice when the source may change, when you want a repeatable calculation, or when you need the split as part of a larger formula. In either case, make sure the separator matches the data and that splitting will not overwrite existing values.

Common problems and how to fix them

The formula reports an error or will not enter. Check that the opening and closing parentheses match and that the text and delimiter are in quotation marks when typed directly. A cell reference such as A2 does not need quotation marks.
The result is missing or has too many pieces. Check whether the delimiter is present exactly as written. If you entered a multi-character delimiter, use FALSE as the third argument when it must be matched as a whole.
There is a blank between two results. That may be a space left after a comma, or an empty field between consecutive delimiters. Use TRIM to remove unwanted surrounding spaces, or set remove_empty_text to FALSE if you need to preserve an empty field.
The formula says it cannot expand. One or more cells where the split results should appear are not empty. Clear those cells or move the formula to an area with enough room. A result that spills across columns cannot overwrite existing content.
The split is not consistent across rows. The records may not use the same delimiter consistently, or the delimiter may also occur inside some values. Inspect the source data and decide whether a simple split is appropriate before applying it to the whole list.

A related misconception is that SPLIT understands the meaning of the text. It does not identify a “first name” or know that a comma inside quotation marks belongs to a single field. It splits wherever the delimiter occurs, following the selected options. If the source has quoted fields or inconsistent formatting, a basic delimiter split may not preserve the intended structure.

Google Sheets SPLIT compared with Excel

This formula is for Google Sheets. Excel for Microsoft 365 has a function called TEXTSPLIT, not SPLIT, and the function names and arguments are not interchangeable. If you move a workbook between the two programs, check the formulas and results rather than assuming a Google Sheets SPLIT formula will work unchanged in Excel.

In Google Sheets, the built-in Data > Split text to columns command is another practical option. Choose the formula when you need results to update with the source; choose the menu when you want to divide selected text as a one-time sheet operation. For either method, keep an untouched copy of the original data until you have confirmed that the output is correct.

Frequently Asked Questions

How do I split text by a space in Google Sheets?
With the text in A2, enter =SPLIT(A2, " "). It separates at ordinary spaces. If the data may contain extra spaces, use =ARRAYFORMULA(TRIM(SPLIT(A2, " "))), but remember that splitting a full name by spaces does not reliably identify first and last names.

Can SPLIT put the results down a column?
Yes. Wrap it in TRANSPOSE. If A2 contains small|medium|large, enter =TRANSPOSE(SPLIT(A2, "|")) in an empty cell to return the values vertically.

How do I split on more than one character?
Use the complete delimiter in the second argument and set the third argument to FALSE. For example, with one::two in A2, =SPLIT(A2, "::", FALSE, TRUE) splits only at the two-colon sequence.

Why does SPLIT return extra columns?
The delimiter may be matching a character inside a value, or the default TRUE setting may be treating each character in a multi-character delimiter separately. Check the source and set split_by_each to FALSE when you need an exact multi-character match.

Can I split text without a formula?
Yes. Select the cells and use Data > Split text to columns. This is handy for a one-time operation; use SPLIT when the result should recalculate as the source text changes.

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.