Google Sheets SPLIT Function: How to Split Text into Columns

To split text into separate columns in Google Sheets, put the original text in A2 and enter =SPLIT(A2,",") in an empty cell. If A2 contains Maya,Chen,Seattle, the formula returns Maya, Chen, and Seattle in three adjacent cells. SPLIT separates text wherever it finds the delimiter you specify; the original cell stays unchanged.

What You’ll Need

A Google Sheets spreadsheet open in a web browser.
A cell containing the text you want to divide, such as a full name, a comma-separated list, or a code with consistent separators.
Enough empty cells to the right of the formula cell for all the parts. The result expands into neighboring cells.

You do not need to install an add-on. SPLIT is a built-in Google Sheets function. If you only need a one-time split, the Data menu also has a Split text to columns command; the formula is more useful when the source data may change and you want the split results to update automatically.

Step-by-Step

Check how the source text is separated. Find the character or text sequence that marks each boundary. Common delimiters include a comma, a space, a hyphen, a slash, or a line break. For example, Lee,Jordan,lee@example.com uses commas, while Lee Jordan uses spaces. Choose a delimiter that does not also occur inside a value you want to keep together.
Make room for the results. SPLIT returns multiple values across cells in the same row, starting in the cell where you enter the formula. If the source is in A2 and could produce three pieces, keep B2, C2, and D2 clear when you put the formula in B2. Existing content or merged cells in the output area can prevent the result from expanding.
Select the output cell and enter a basic formula. With Maya,Chen,Seattle in A2 and an empty output area beginning at B2, enter =SPLIT(A2,",") in B2. The result is Maya in B2, Chen in C2, and Seattle in D2. Press Enter to apply the formula. In this example, the comma is the delimiter, and Google Sheets places each segment in a separate cell.
Use a space or another delimiter when appropriate. With Jordan Lee in A2 and empty cells to the right of B2, enter =SPLIT(A2," ") in B2. The result is Jordan in B2 and Lee in C2. The space between the quotation marks is the delimiter. For a hyphenated code such as AB-204-NZ, use a hyphen between the quotation marks instead. Keep the delimiter inside quotation marks so Sheets treats it as text.
Choose whether the delimiter is an exact sequence. Sometimes the delimiter contains more than one character, such as a comma followed by a space. With Maya, Chen, Seattle in A2, enter =SPLIT(A2,", ",FALSE,TRUE) in B2. The result is Maya, Chen, and Seattle in three cells. The third argument, FALSE, tells Sheets to match the comma-and-space sequence as a whole rather than treating the comma and space as separate delimiter characters. This is useful when you need precise control over what counts as a boundary.
Decide how to handle repeated delimiters. By default, SPLIT removes empty results between adjacent delimiters. With A,,C in A2, the formula =SPLIT(A2,",",TRUE,TRUE) in B2 returns A and C in adjacent cells; it does not preserve the blank middle field. To retain that blank field, enter =SPLIT(A2,",",TRUE,FALSE) in B2 instead. That result is A, an empty cell between the values, and C. The fourth argument controls whether empty text is removed.
Apply the formula to more rows if needed. For a small list, enter the formula for the first row, then use the fill handle—the small square at the selected cell’s lower-right corner—to copy it down. For instance, if each source row is in column A, copy the formula in B2 into B3 and later rows; the relative reference changes to A3, A4, and so on. Confirm that the cells to the right are clear on every row, because each result needs room to expand horizontally.
Use the menu for a one-time split instead. Select the cells containing the text, then choose Data > Split text to columns. Use the separator selector that appears to choose a detected separator or a specific option. Review the results before continuing: this command places the split pieces into neighboring columns and is intended for a direct data transformation, not a live formula linked to the original cell.

The basic pattern is to give SPLIT a text value or cell reference and a delimiter. Optional arguments let you control whether each character in a multi-character delimiter is treated separately and whether empty pieces are kept. You can leave optional arguments out for everyday comma- or space-separated values, because Sheets applies defaults.

Common Mistakes

Using the wrong delimiter: A formula looking for a comma will not split text separated by semicolons or tabs. Inspect a source value closely; what looks like a space might be a tab, and pasted data may use a different punctuation mark than expected.
Forgetting quotation marks around a literal delimiter: A comma or space typed as the delimiter must be in quotation marks, as in =SPLIT(A2,","). If the delimiter is in a cell, you can instead refer to that cell, provided it contains the exact separator you want to use.
Assuming the source cell will be divided in place: SPLIT writes its results in the formula cell and the cells beside it. It does not erase or replace the original text. Put the formula where the output can expand, then keep the source column if you still need it.
Ignoring blank fields: Consecutive separators can represent missing data. The default removes empty pieces, which may shift later values left. Set the fourth argument to FALSE when an empty field must remain in its position.
Putting data in the output range: A formula may show an expansion error if neighboring cells already contain content. Even a space or a merged cell can interfere. Clear the needed output range or move the formula to a location with enough room.
Expecting SPLIT to understand meaning: The function separates characters; it does not know that a comma inside a quoted address or a person’s name should be treated differently. If a delimiter also occurs within values, choose a more reliable separator or clean the source data first.

Tips

Test one representative row first. Before filling a formula down hundreds of records, check a row with the longest or messiest example. It can reveal extra spaces, missing separators, or unexpected blank fields before you apply the approach broadly.
Use a distinctive delimiter when preparing data. If you control the source format, choose a separator that does not appear in the values. A pipe character, for example, may be a safer boundary than a regular space when names or descriptions can contain spaces.
Separate on a full delimiter when needed. SPLIT normally treats the characters in a multi-character delimiter individually. Set the third argument to FALSE when the whole sequence must match, such as comma-plus-space. This avoids splitting at a space that appears on its own.
Keep formulas and source data organized. Put the original values in one column and the formula outputs in the columns beside them. Label the result columns so it is clear what each piece represents, and avoid typing other data into the cells where results need to appear.
Choose the right method for the job. Use the formula when results should refresh as source text changes. Choose Data > Split text to columns when you want to split selected values directly and do not need a formula-based relationship to the original entries.
Check spreadsheet locale if a formula will not parse. Google Sheets settings can affect formula argument separators. If a comma-separated formula gives a parse error despite being typed correctly, check File > Settings and the spreadsheet’s locale. Depending on settings, the formula may require semicolons between arguments. The comma inside quotation marks remains the delimiter in the examples above.

Troubleshooting

The formula returns the original text as one piece. Check that the delimiter in the formula exactly matches the text. Look for a different punctuation character, an extra space, or a tab. If the separator is a comma followed by a space, decide whether to split on the comma alone or on the exact two-character sequence. You can also use Data > Split text to columns to inspect how Sheets detects the separator.

You see an array expansion or overwrite error. Clear the cells where the output would go, including cells to the right of the formula. Check for merged cells in that area and unmerge them if appropriate. If you need to keep existing data, move the formula to a blank range rather than deleting information that matters.

The pieces appear in the wrong columns or values seem to disappear. Check for repeated delimiters and review the default removal of empty results. If each separator marks a meaningful field, use FALSE for the fourth argument so empty segments are retained. Also confirm that the delimiter does not occur inside a value that should stay intact.

The formula displays a parse error. Make sure the opening and closing parentheses are present, the text delimiter is surrounded by straight double quotation marks, and the cell reference is valid. If the formula was copied from a document, replace curly quotation marks with straight ones. If commas between arguments are rejected, check the spreadsheet locale and try the argument separator appropriate to that setting.

The formula works in one row but not another. Inspect the other source cells for missing separators, trailing separators, or a different text pattern. SPLIT can return different numbers of pieces for different rows, so verify that the output cells for every row are available and that each source uses a consistent format.

Frequently Asked Questions

Does SPLIT change or delete the original text?
No. The formula returns separate results in the cell where it is entered and adjacent cells. Your source text remains in its original cell unless you edit or remove it yourself.

Can I split text into rows instead of columns?
SPLIT returns its pieces horizontally across columns. If you need a vertical list, you can transpose the resulting range using Edit > Paste special > Transposed after copying it, or use another formula approach suited to your sheet. The menu’s Split text to columns command also places results across columns.

How do I keep empty fields between two delimiters?
Set the fourth argument of SPLIT to FALSE. For example, the step-by-step example with A,,C retains an empty middle result when that argument is FALSE. This is helpful when each position represents a separate field, even if one field is missing.

Can SPLIT use more than one delimiter?
Yes. A delimiter argument can contain multiple characters, and by default each character is treated as a separate delimiter. Set the third argument to FALSE if the characters must appear together as one exact sequence. If you need to split on several unrelated separators, test the behavior carefully with representative data.

What is the difference between SPLIT and Split text to columns?
SPLIT is a formula that keeps its output connected to the source and recalculates when the source changes. Data > Split text to columns is a menu command for splitting selected data directly. Use the formula for a repeatable, updating result; use the menu for a one-time change.

Does this work in Microsoft Excel too?
This guide uses Google Sheets. Excel has different functions and menu behavior; its modern TEXTSPLIT function is available in Microsoft 365 and Excel 2024. Do not assume a Google Sheets SPLIT formula can be pasted into Excel unchanged.

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.