Google Sheets has a SPLIT function that separates text into neighboring cells using a delimiter such as a comma, space, or symbol. Enter =SPLIT(A2, ",") when A2 contains comma-separated text; the pieces spread across the row. For an exact multi-character delimiter, set the third argument to FALSE.
What the SPLIT function does
SPLIT takes a text value and breaks it wherever it encounters a specified delimiter. A delimiter is the character or text marking the boundary between pieces: a comma in a list, a pipe between fields, or a space between words, for example. The result is not a single joined string. Sheets places the separated pieces into cells to the right of the formula cell.
This makes SPLIT useful for turning simple, consistently formatted text into separate columns. A contact record such as Ravi Shah, ravi@example.com, 555-0142 could become three cells: a name, an email address, and a phone number. The function does not understand what those fields mean; it only follows the delimiter you provide.
SPLIT is available in Google Sheets in a web browser. If you are working in Microsoft Excel, its comparable function is generally TEXTSPLIT, available in Microsoft 365 and Excel 2024; Excel does not use Google Sheets’ SPLIT function name. Excel’s Text to Columns tool is another option.
SPLIT syntax and its options
The syntax is SPLIT(text, delimiter, [split_by_each], [remove_empty_text]). The square brackets indicate optional arguments; do not type the brackets into the formula. The first argument is the text or cell to divide, and the second is the delimiter. The remaining arguments control how Sheets treats delimiter characters and empty pieces.
text: the text to split, often a cell reference such as A2.delimiter: the character or string that marks each split point.split_by_each: when TRUE, the delimiter’s individual characters can each act as a separator. This is the default.remove_empty_text: when TRUE, empty pieces created by repeated delimiters are removed. This is also the default.
Because both optional settings default to TRUE, a short formula can behave differently than expected when the delimiter contains more than one character. To make the behavior clear, include the optional arguments when the exact delimiter or blank fields matter.
Basic example: splitting a comma-separated list
Suppose A2 contains apples,oranges,pears. With that text in A2, enter =SPLIT(A2, ",") in B2. The result is apples in B2, oranges in C2, and pears in D2. The formula uses a comma as the delimiter and relies on the default settings.
The output cells are generated by the formula; you do not need to copy the formula across each column. If the source text changes, the separated results update. To apply the same approach to multiple records, put one source string per row and use a formula beside the first one, then fill the formula down. Check that the neighboring output columns are free for every row before doing so.
A delimiter must match the text. For example, a comma-space delimiter is not always interchangeable with a comma: the space may remain at the start of each resulting piece when only the comma is used. If the text has a consistent comma followed by a space, you can specify both characters as the delimiter.
Using an exact multi-character delimiter
By default, Sheets treats each character in the delimiter as a possible separator. That matters when the delimiter is a combination of characters. Imagine A2 contains Maya Chen | Sales | West and the intended fields are a person, a department, and a region. With the text in A2, enter =SPLIT(A2, " | ", FALSE, TRUE) in B2. It returns Maya Chen, Sales, and West in B2:D2.
Here, FALSE tells Sheets to look for the full delimiter—a space, vertical bar, and space—instead of treating its characters separately. The final TRUE removes empty pieces if repeated instances create any. This exact-match setting is often helpful for structured text separated by a token such as :: or - .
If you leave split_by_each as TRUE in this example, the spaces and vertical bars can each trigger a split. That would break the person’s two-word name as well as the fields, producing more pieces than intended. When a delimiter contains multiple characters, decide whether you mean the whole sequence or any one of its characters.
Keeping empty fields and choosing delimiters
Consecutive delimiters can represent a missing value. For instance, red,,blue may mean that the middle field was deliberately left blank. With that text in A2, enter =SPLIT(A2, ",", FALSE, FALSE) in B2. The result includes red, an empty cell, and blue. The last FALSE instructs Sheets not to discard empty pieces.
Leaving the final argument at its default TRUE removes empty pieces, so later values may shift left. That can be a serious issue if each position has a defined meaning—for example, if the second item must always be a middle name or a product code. Decide whether blanks are meaningful before choosing the setting.
It is also important to distinguish between several possible delimiters and one exact delimiter. With the text north-east/west in A2, the formula =SPLIT(A2, "-/", TRUE, TRUE) treats either a hyphen or slash as a separator and produces north, east, and west. If you wanted to split only at the exact sequence -/, use FALSE for the third argument instead. The data format determines which interpretation is correct.
Using the menu instead of a formula
For a one-time split, the menu can be quicker than writing a formula. Select the cells containing the text, then choose Data > Split text to columns. Use the separator control that appears to choose a detected separator or specify one. Sheets places the results into adjacent columns, so make sure those cells do not contain information you need to keep.
The menu is useful when you want to inspect and separate a static batch of data. A SPLIT formula is usually better when the source values may change, when you want the process to update automatically, or when the delimiter needs to be controlled explicitly in a formula. The menu operation changes the selected data into separate values; it does not leave a formula that recalculates from the original text.
For example, you might use the menu to separate a downloaded list of names once, then use a formula for a weekly import that is refreshed regularly. In either case, keep an untouched copy of the original data until you have confirmed the split worked as intended.
Common problems and how to fix them
- The pieces appear in the wrong columns: Check whether your delimiter is being interpreted character by character. Set
split_by_eachto FALSE when the whole multi-character string must match. - Spaces remain around the values: Include the surrounding spaces in the delimiter if they occur consistently, or clean the results separately. Do not assume every record uses identical spacing.
- A result cannot expand: SPLIT needs empty cells to the right of the formula. Clear any conflicting contents in the output range, or move the formula to a clear area. Sheets may report that an array result could not expand because it would overwrite data.
- Some rows split differently: The source may contain inconsistent separators, such as a comma in one row and a semicolon in another. Standardize the source or handle the formats separately rather than assuming one delimiter will cover both.
- Some text is missing: Check for consecutive delimiters and the
remove_empty_textsetting. With the default TRUE, empty pieces are removed.
One common misconception is that SPLIT can identify fields by meaning. It cannot tell a comma that belongs inside a quoted address from a comma that separates fields; it simply splits at the delimiter. If your imported data uses quoted fields, embedded delimiters, line breaks, or inconsistent formatting, a basic SPLIT formula may not be reliable. Use a properly formatted CSV import or clean the data with a method designed for its structure.
Google Sheets SPLIT compared with Excel
Google Sheets’ SPLIT and Excel’s TEXTSPLIT serve similar purposes, but their names and argument behavior are not identical. In Excel for Microsoft 365 or Excel 2024, TEXTSPLIT can separate text into columns using a column delimiter. In Google Sheets, SPLIT is the corresponding familiar function and defaults to treating the characters in a multi-character delimiter individually. In Excel, TEXTSPLIT generally treats the delimiter argument as a complete string unless configured otherwise.
For example, with Maya Chen | Sales | West in A2, an Excel user can enter =TEXTSPLIT(A2," | ") in B2 to split at the exact string. This is an Excel formula, not a Google Sheets formula. Older Excel versions may not include TEXTSPLIT, so check the version or use Excel’s Data > Text to Columns command. Do not paste a Sheets formula into Excel and assume the function name will work.
Frequently Asked Questions
Does Google Sheets have a SPLIT function?
Yes. SPLIT separates text at a specified delimiter and places the resulting pieces into adjacent cells. It is a Google Sheets function; Excel uses other tools, including TEXTSPLIT in supported versions.
How do I split text into separate columns in Google Sheets?
Use a formula such as =SPLIT(A2, ",") if A2 contains comma-separated text. Alternatively, select the text cells and choose Data > Split text to columns for a one-time split.
Why does SPLIT divide at spaces as well as a symbol?
The default split_by_each setting is TRUE, so each character in a multi-character delimiter can act as a separator. Add FALSE as the third argument when Sheets should match the entire delimiter string.
Can SPLIT put the results down rows instead of across columns?
SPLIT returns its pieces horizontally across columns. If your workflow requires a vertical list, you need an additional transformation or a different approach; SPLIT alone does not switch the output orientation.
Does SPLIT change the original text?
A formula does not replace the source cell; it displays the separated results elsewhere. The menu command Data > Split text to columns operates on the selected data, so preserve a copy if you need the original string.
Related
- Accounting Trial Balance Worksheet: How It Works
- Google Sheets SPLIT Function: How to Separate Text
- Oracle Split String Example: SQL Methods That Work
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- XFD Column Number in Excel: What 16,384 MeansXFD is column number 16,384 in modern Microsoft Excel. It is the last column available on an Excel…
- Compare Documents in Word: A Step-by-Step GuideTo compare two versions of a document in Microsoft Word, open Word and choose Review > Compare >…
- How to Find Blank Rows in ExcelTo find rows with no data in a particular range, add a helper column with =COUNTBLANK(A2:F2)=COLUMNS(A2:F2), fill it…
- Excel 3-Color Scale: How It Works and When to Use ItIn Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting >…
- Google Sheets: Add a Filter to a Column and Sort RowsTo add a filter in Google Sheets, select the data range, including its header row, then choose Data…
- How to Delete a Page in a Word Template Without Breaking ItTo delete a page from a Word template, open the actual template file, select the page’s contents with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.