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
- Open your Google Sheets document in a web browser.
- Click on the cell where you want the first result to appear (e.g., B2).
- Type
=SPLIT(and then click the cell containing the text (e.g., A2). - Type a comma, then the delimiter in quotes, e.g.,
",". - 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,
TEXTSPLITworks 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_eachto FALSE. For instance,=SPLIT("a--b--c", "--", FALSE)returns “a”, “b”, “c”. - Empty cells appearing: Set
remove_empty_textto 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
- Excel Template for Saving Goal Tracking
- Excel Template for Project Expense Tracking
- Excel Template for Paying Off Debt: Free & DIY
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Custom Data Validation: Rules and TipsTo create custom data validation in Google Sheets, select the cells, choose Data > Data validation, click Add…
- JavaScript split() Explained: Strings, Limits, and ExamplesIn JavaScript, split() is a string method that divides a string wherever a specified separator occurs and returns…
- Excel Online Import CSV: Steps and Common FixesTo import a CSV into Excel Online, upload the file to OneDrive and open it with Excel for…
- How to Create a Reusable Task Template in OutlookTo create a reusable task template, use classic Outlook for Windows: make a task with the fields you…
- Cash Flow Forecast Excel Template: Build One That WorksA cash flow forecast Excel template lists expected cash receipts and payments by week or month, then calculates…
- Conditional Formatting When a Cell Is Blank in ExcelTo highlight blank cells in Excel for Microsoft 365 on Windows, select the cells, then choose Home >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.