How to Use SPLIT in Google Sheets (Step-by-Step)

How-to basics · Updated November 22, 2026

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

  1. 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,NY into cell A1.
  2. 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.
  3. Type the SPLIT formula. In the selected cell, enter =SPLIT(A1, ",") if your data is in A1 and you’re splitting by a comma. Replace A1 with the actual cell reference and "," with your delimiter. Press Enter.
  4. Check the result. The text in A1 will be divided across B1, C1, D1, and so on. For John,Smith,NY, B1 shows John, C1 shows Smith, and D1 shows NY.
  5. Split by a space. To split a full name like Jane Doe into first and last, use =SPLIT(A2, " "). Note that if there are multiple spaces, you might get empty cells—use the TRIM function first if needed.
  6. 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.
  7. Control how empty cells are handled. The third argument of SPLIT is split_by_each (TRUE or FALSE). The fourth is remove_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.
  8. Use SPLIT with other functions. Wrap SPLIT inside functions like INDEX to grab a specific piece. For example, =INDEX(SPLIT(A5, ","), 1, 2) returns the second item from the split.
  9. Split an entire column at once. Use ARRAYFORMULA with 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.
  10. 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 TRIM before 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 TRANSPOSE to put results in a column instead of a row: =TRANSPOSE(SPLIT(A1, ",")) stacks the items vertically.
  • Use QUERY or FILTER on 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 Smith and 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

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.