The SPLIT function in Google Sheets divides text into separate cells based on a delimiter you specify—like a comma, space, or semicolon. It’s the quickest way to break a full name into first and last, turn a comma-separated list into columns, or separate data you imported from another system. You don’t need any add-ons or scripting; just type the formula and let Sheets handle the rest.
What You’ll Need
- A Google account and a browser (Chrome, Firefox, Safari, Edge).
- A Google Sheets file with text in one or more cells that contains a consistent delimiter (e.g., a comma, space, or pipe).
- Basic familiarity with typing formulas into a cell.
Step-by-Step
- Open your Google Sheet. Go to sheets.google.com and open the file you want to edit. If you’re starting fresh, create a new sheet and type something like
John,Smith,NYinto cell A1. - Select the cell where you want the split results to start. Usually this is a cell to the right of your data, like B1, but it can be anywhere. Make sure there’s enough empty space to the right—SPLIT will fill multiple cells horizontally.
- Type the SPLIT formula. In the selected cell, enter
=SPLIT(A1, ",")if your data is in A1 and you’re splitting by a comma. ReplaceA1with the actual cell reference and","with your delimiter. Press Enter. - Check the result. The text in A1 will be divided across B1, C1, D1, and so on. For
John,Smith,NY, B1 showsJohn, C1 showsSmith, and D1 showsNY. - Split by a space. To split a full name like
Jane Doeinto first and last, use=SPLIT(A2, " "). Note that if there are multiple spaces, you might get empty cells—use theTRIMfunction first if needed. - Split by multiple delimiters. You can pass an array of delimiters inside curly braces. For example,
=SPLIT(A3, {",", ";"})splits by either a comma or a semicolon. This is handy for messy data. - Control how empty cells are handled. The third argument of SPLIT is
split_by_each(TRUE or FALSE). The fourth isremove_empty_text(TRUE or FALSE). For instance,=SPLIT(A4, ",", FALSE, TRUE)treats the delimiter as a whole string (so,,won’t split) and removes any empty cells from the result. - Use SPLIT with other functions. Wrap SPLIT inside functions like
INDEXto grab a specific piece. For example,=INDEX(SPLIT(A5, ","), 1, 2)returns the second item from the split. - Split an entire column at once. Use
ARRAYFORMULAwith SPLIT to process multiple rows. In B1, enter=ARRAYFORMULA(SPLIT(A1:A10, ","))to split all ten cells. This will spill results into columns B, C, D, etc. for each row. - Use the Split text to columns menu (alternative). If you prefer not to write a formula, select the column with data, go to Data > Split text to columns, and choose a separator from the dropdown that appears (comma, space, semicolon, custom). This method overwrites the original data with the split parts, so it’s best for one-time cleanups.
Common Mistakes
- Forgetting the delimiter as a string. SPLIT needs the delimiter in quotes:
=SPLIT(A1, ",")works, but=SPLIT(A1, ,)gives a formula parse error. - Not leaving enough space for results. If the cell to the right already has data, SPLIT will overwrite it. Always clear the target area first.
- Using SPLIT on data with inconsistent delimiters. If some rows use commas and others use semicolons, a single delimiter won’t work. Use an array of delimiters or clean the data first.
- Expecting SPLIT to change the original data. SPLIT is a formula—it outputs results to new cells. The original text stays unchanged. If you want to replace the original, use the menu method or copy and paste values.
- Using SPLIT on a whole column without ARRAYFORMULA. Typing
=SPLIT(A1:A10, ",")in a single cell will only split the first row in most cases. You need ARRAYFORMULA to process each row individually.
Tips
- Use
TRIMbefore splitting to remove extra spaces:=SPLIT(TRIM(A1), ",")avoids empty cells from leading/trailing spaces. - Split by newline using
CHAR(10)as the delimiter. For example,=SPLIT(A1, CHAR(10))splits text that has line breaks into separate cells. - Combine SPLIT with
TRANSPOSEto put results in a column instead of a row:=TRANSPOSE(SPLIT(A1, ","))stacks the items vertically. - Use
QUERYorFILTERon the split results to further analyze the data, since SPLIT outputs a range you can reference. - For splitting by a character that’s also a regex special character (like a period or question mark), you may need to escape it with a backslash. For example, to split by a period, use
=SPLIT(A1, "\\."). - If you split a column with the menu, you can choose “Custom” and type a delimiter that isn’t in the list, like a pipe (|).
Troubleshooting
- #VALUE! error usually means the delimiter isn’t found, or the text is empty. Double-check that the delimiter exists in the cell. For example, if A1 is
John Smithand you use a comma, you’ll get an error because there’s no comma. - Formula parse error often comes from an unquoted delimiter or a missing parenthesis. Re-type the formula carefully, and make sure commas are inside quotes.
- Results spill into cells that already have data—Sheets will overwrite them without warning. If you see unexpected values, undo (Ctrl+Z / Cmd+Z) and clear the destination area before re-entering the formula.
- SPLIT only returns the first item when you drag down. This happens because SPLIT is not designed to auto-expand down a column. Use ARRAYFORMULA as shown in Step 9, or copy the formula down manually.
- Empty cells appear in the output. If your text has consecutive delimiters (e.g.,
a,,b), SPLIT will include an empty cell by default. Set the fourth argument to TRUE:=SPLIT(A1, ",", TRUE, TRUE)to remove empty text. - Split by a space creates extra columns because of multiple spaces. Use
=SPLIT(TRIM(A1), " ")to collapse multiple spaces into one.
Frequently Asked Questions
Can SPLIT split text into rows instead of columns?
Yes. Wrap SPLIT with TRANSPOSE: =TRANSPOSE(SPLIT(A1, ",")) will place each item in a separate row, going down.
Does SPLIT work in Microsoft Excel?
No. Excel has a similar function called TEXTSPLIT (available in Microsoft 365), but it works differently. In Excel, you can also use Text to Columns from the Data tab. Google Sheets’ SPLIT is unique to Sheets.
Can I split by multiple characters at once?
Yes, you can pass an array of delimiters: =SPLIT(A1, {",", ";"}) splits by either a comma or a semicolon. Note that this uses the “split by each” logic, so each character is treated individually.
How do I keep the original data and put split results elsewhere?
SPLIT as a formula always leaves the original cell untouched. Just type the formula in a different cell. If you use the Data > Split text to columns menu, it overwrites the original—so copy the data first if you need to keep it.
Why does SPLIT return a #REF! error?
This usually happens when the result would overwrite existing cells. For example, if you have data in B1 and your split needs to fill B1, Sheets will show #REF!. Clear the destination cells or move the formula to a location with more empty space.
Can I split text that has no delimiter?
No, SPLIT needs a delimiter. If your text has no consistent separator, you might use other functions like REGEXEXTRACT or MID to pull out specific parts based on position.
Related
- How to Update a Dictionary in Python
- Value Dollar Shop Jurong East: Location, Prices & What to Expect
- How to Select All on a Laptop: 5 Fast Ways
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Jira Merge Table Cells: Steps and LimitationsYes—Jira’s current rich-text editor can merge adjacent cells in a table when the Merge cells command is available.…
- Snowflake SQL SPLIT: Syntax, Examples, and AlternativesIn Snowflake SQL, use SPLIT(string, delimiter) to divide text wherever an exact delimiter occurs; the function returns an…
- Excel Split Cell Color in Half: What WorksExcel cannot give one cell two separate solid fill colors, but you can create the appearance of a…
- Excel COUNT vs Count Numbers: What’s the Difference?In Excel, COUNT counts cells containing numbers, while the ribbon command called Count Numbers inserts that same function.…
- Enum Data Type: Meaning, Uses, and ExamplesAn enum data type is a type whose values are defined as a finite set of named choices.…
- How to Encrypt an Excel File With a PasswordTo encrypt an Excel file in Microsoft 365 for Windows, open it and select File > Info >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.