Google Sheets SPLIT Formula: Separate Text into Columns

In Google Sheets, use the SPLIT function to divide text at a chosen separator and place the pieces in neighboring cells. If the text is in A2 and commas separate its fields, enter =SPLIT(A2,",") in an empty cell; the results will spill across the row.

What the SPLIT function does

SPLIT breaks one text value into smaller parts wherever it finds a delimiter, such as a comma, space, hyphen, or vertical bar. It is useful when a cell contains several values joined together—for example, a full name, product code, or category and region—and you want those values in separate cells for sorting, filtering, or analysis.

The function returns an array of values rather than combining the pieces into one result. In the usual case, the pieces appear in columns to the right of the formula cell. The original cell is not changed: the formula creates a separate, calculated result, so the source text remains available.

The syntax is SPLIT(text, delimiter, [split_by_each], [remove_empty_text]). The first two arguments are required. The optional arguments control how Sheets interprets the delimiter and whether it keeps empty pieces created by repeated delimiters.

Split a cell with the basic formula

Suppose A2 contains Mira Chen,Sales,West, with commas separating a name, department, and region. With that text in A2, put this formula in B2:

=SPLIT(A2,",")

The result is Mira Chen in B2, Sales in C2, and West in D2. The comma inside the formula’s quotation marks is the delimiter: it tells Sheets where to cut the source text. Replace it with the character that actually separates your values.

For example, if A2 contains red|blue|green, use the same formula structure with a vertical bar as the delimiter. The delimiter must be written as text in quotation marks. If the source cell is empty or has no matching delimiter, the result may simply be the original text rather than several pieces.

To split many rows, enter the formula for the first row and copy it down. For example, with source values in A2:A20, put the formula in B2 and fill it down alongside the data. Each row’s formula refers to that row’s A cell when the reference is relative.

Choose the right delimiter behavior

By default, split_by_each is TRUE. That means Sheets treats every character in the delimiter argument as a separate delimiter. This default is handy when either a comma or semicolon can separate fields: putting both characters in the delimiter argument makes either one a stopping point.

However, the default can be surprising when the separator contains more than one character. If A2 contains North||West and the two vertical bars together are one separator, set split_by_each to FALSE. With that text in A2, this formula treats the pair as a single delimiter:

=SPLIT(A2,"||",FALSE,TRUE)

The last TRUE keeps the default behavior of removing empty results. If you left split_by_each as TRUE, Sheets would treat each bar as its own separator, not as the two-character sequence ||. Use FALSE when the full delimiter string must match exactly.

For a one-character delimiter, the default is generally appropriate, so the shorter formula is easier to read. Set optional arguments explicitly when the data has a special case or when a coworker should be able to see the intended behavior clearly.

Handle spaces and empty fields

SPLIT separates text but does not automatically remove spaces around each piece. If A2 contains Mira, Chen, splitting on a comma produces Mira and Chen; the second result begins with a space. That extra whitespace can make two values look the same while behaving differently in comparisons or lookups.

With the comma-separated text in A2, wrap the split result in TRIM and ARRAYFORMULA to remove leading and trailing spaces from the returned pieces:

=ARRAYFORMULA(TRIM(SPLIT(A2,",")))

Check the results after cleaning: TRIM also reduces repeated spaces within text to a single space. If repeated internal spaces are meaningful in your data, do not apply it without considering that change.

By default, Sheets removes empty pieces. For instance, two consecutive commas in A,,C create an empty field between A and C, but the default setting omits that empty result. If the position of the blank field matters, set remove_empty_text to FALSE. With A,,C in A2, this formula preserves the empty middle result:

=SPLIT(A2,",",FALSE,FALSE)

The results are A, a blank cell, and C. Here FALSE for split_by_each is harmless because the delimiter is only one character; the final FALSE is what preserves the empty field. Empty fields can be important for structured records where each position has a meaning, such as first name, middle name, and last name.

Use a formula or Split text to columns?

A formula is best when you want the split result to update automatically as the source text changes. It also keeps the original data intact and can be copied down a column. In Google Sheets in a web browser, select the source cells and choose Data > Split text to columns for a one-time split. Sheets offers a separator choice, including a custom separator.

The menu command writes separated values into neighboring cells rather than leaving a formula that recalculates from the original. Use it when you are cleaning a fixed data set and want the results as ordinary cell contents. Before using it, make sure the cells to the right are clear: existing content there could be overwritten.

In short, choose SPLIT for repeatable, formula-driven work, and choose Data > Split text to columns for a quick transformation of selected data. If you need to preserve the original, duplicate the source column before using the menu command.

Plan for the formula’s output cells

Because SPLIT returns several values, the formula needs room to expand horizontally. Put it in a cell with empty cells to its right. If an existing value blocks the output, Sheets cannot expand the array and may show a #REF! error explaining that the result was not expanded because it would overwrite data. Clear the blocking cell or move the formula to a clear area.

Consider the widest possible result before filling formulas down. One row might split into three pieces and another into six, so a formula that works at the top of a sheet may run into existing content farther down. Leave enough columns for the maximum number of parts, and avoid placing unrelated data in the spill area.

The output is also not a permanent snapshot. When the source text changes, the formula recalculates and the displayed pieces can change. That is helpful for live data, but if you need fixed results for a handoff or archive, copy the results and use Edit > Paste special > Values only.

Common problems and limitations

Nothing splits: Check that the delimiter exactly matches a character in the source. A hyphen, en dash, and em dash look similar but are different characters.
Extra spaces appear: Use TRIM when leading or trailing spaces should be removed, and inspect the source for inconsistent spacing.
Empty values disappear: Set remove_empty_text to FALSE if repeated delimiters represent a meaningful blank field.
The result shows an expansion error: Clear the cells where the returned pieces need to go, or move the formula to an unobstructed range.
Quoted commas break a record: SPLIT looks for the delimiter; it does not interpret CSV quotation rules. A value such as "Smith, Jane",Sales can be split at the comma inside the quoted name. For properly structured CSV files, use a suitable import or parsing workflow rather than assuming a simple split will honor quoted fields.

Also remember that splitting on a space is literal: it does not understand names or sentences as special data types. A name with a middle name will produce an extra piece, and repeated spaces may affect the output. Review a sample of the results before applying a formula to a large data set.

How SPLIT compares with Excel

SPLIT is a Google Sheets function. In Microsoft Excel, the related function is TEXTSPLIT, which has different syntax; copying a Sheets formula that uses SPLIT directly into Excel will not work. Excel’s TEXTSPLIT is available in Microsoft 365 and Excel 2024, while the Google Sheets formulas in this article are intended for Sheets in a web browser.

That distinction matters when sharing a workbook or moving data between spreadsheet programs. The underlying idea—separating text at a delimiter—is similar, but function names, optional arguments, and behavior should be checked in the destination application. For a formula-free split in Sheets, the menu option is often simpler than translating a formula.

Frequently Asked Questions

How do I split text into columns in Google Sheets?
Use =SPLIT(A2,",") when A2 contains comma-separated text, changing the comma to your actual separator. To split selected data without a formula, choose Data > Split text to columns.

Can Google Sheets split text by a space?
Yes. Set the delimiter to a space in the formula. The function will split at each matching space, so a full name with a middle name can produce three pieces rather than two.

How do I split on more than one delimiter?
With the default split_by_each setting, provide the delimiter characters together to treat each one as a possible separator. Set that argument to FALSE when the combined characters must match as one multi-character delimiter.

Why does SPLIT give me a #REF! error?
The formula’s output needs neighboring cells, and something may be blocking its expansion. Clear the cells where the split pieces should appear, or place the formula somewhere with enough empty space.

Does SPLIT work on a whole column automatically?
A basic SPLIT formula works on the text value you give it. For a list of rows, copy the formula down alongside the source values, checking that each row has room for the widest expected result.

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.