Google Sheets SPLIT Function: How to Separate Text

How-to basics · Updated November 22, 2026

To divide text from one cell into separate columns in Google Sheets, enter a formula such as =SPLIT(A2," ") in an empty cell beside it; this example assumes the text to split is in A2 and separates it wherever there is a space. The results fill cells to the right, so leave those cells clear. You can change the delimiter, such as a comma or hyphen, to match your data. SPLIT is useful for separating names, addresses, codes, and lists without retyping each part.

What You’ll Need

  • A Google Sheets spreadsheet open in a web browser, with the text you want to divide in a cell.
  • Empty cells to the right of the formula cell for the results to fill into. If the text has several parts, make sure enough adjacent columns are empty.
  • A clear idea of the separator between the parts, such as a space, comma, hyphen, or another character. If the same text uses inconsistent separators, you may need to clean it before splitting.

Step-by-Step

  1. Check how the source text is arranged. Find a sample value and identify the exact character between the pieces you want to separate. For example, Ava Patel has a space between the first and last name, while Patel, Ava has a comma and a space. The delimiter you choose determines where Google Sheets makes each split. If the source values have different patterns, test a few before applying the formula to a full list.
  2. Choose a clear destination cell. Click an empty cell in the same row as the source, preferably the cell immediately to its right. SPLIT returns multiple results across the row, so check that the cells to the right are empty. Do not put the formula over the source data unless you have already copied that data elsewhere.
  3. Enter the basic SPLIT formula. With a full name in A2 and a space between the first and last name, enter =SPLIT(A2," ") in B2. Press Enter. The first name appears in B2 and the last name in C2. If A2 contains Ava Patel, the outputs are Ava and Patel. The formula refers to A2, so changing the original text updates the results automatically.
  4. Use the right delimiter for your data. With comma-separated text such as red,green,blue in A2, enter =SPLIT(A2,",") in B2 to put each color in a separate column. This example assumes the entire list is in A2 and the comma is the separator. For a hyphenated code, use a hyphen as the delimiter; for a slash-separated date-like string, use a slash. Enter the delimiter inside straight quotation marks in the formula.
  5. Decide how spaces around delimiters should be handled. If A2 contains Patel, Ava, splitting on a comma leaves a leading space before Ava. You can instead use an exact comma-and-space delimiter. With Patel, Ava in A2, enter =SPLIT(A2,", ",FALSE,TRUE) in B2. The third argument, FALSE, tells Sheets to treat the comma and following space together as one exact delimiter. This returns Patel and Ava without the extra space.
  6. Understand the optional arguments. The function’s pattern is SPLIT(text, delimiter, [split_by_each], [remove_empty_text]). The first argument is the cell or text to divide; the second is the delimiter. The third argument controls how a delimiter made of multiple characters is interpreted. Its default is TRUE, meaning each character in the delimiter is treated separately. Set it to FALSE when the entire delimiter string must be matched. The fourth argument determines whether empty results between repeated delimiters are removed; it is TRUE by default.
  7. Use multiple single-character separators when useful. If A2 contains red,green;blue and either a comma or semicolon should mark a split, enter =SPLIT(A2,",;") in B2. This assumes the text is in A2 and the two separators are comma and semicolon. Because the third argument is omitted, Sheets treats each character in the delimiter string as a separator. Use this approach only when each character should trigger a split on its own.
  8. Apply the formula to more rows. After confirming the result for the first row, click its formula cell and use the small square at the lower-right corner of the selection to drag the formula down. For example, if the source values are in A2:A20, place the first formula in B2 and fill down alongside the records. Sheets adjusts the reference by row. Confirm that the output area remains clear for every row, since each formula may return a different number of columns.
  9. Use the menu option when you want to split values in place. If you do not need a formula that updates from the original text, select the cells containing the values and choose Data > Split text to columns. Choose the separator in the control that appears, or use automatic detection if it identifies the delimiter correctly. This changes the selected data into separate columns rather than creating formula results. Check the cells to the right first, because existing content can be replaced or block the split.

For a vertical result instead of the usual horizontal one, you can transpose the split output. With a comma-separated list in A2, enter =TRANSPOSE(SPLIT(A2,",")) in B2. It returns each item down a column. Keep the cells below B2 clear so the results have room to expand.

Common Mistakes

  • Choosing a delimiter that does not match the text. A formula that splits on a comma will not divide text separated by semicolons. Inspect the source value, including punctuation and spaces, before choosing the delimiter.
  • Forgetting that a space is part of the result. Splitting on a comma in Patel, Ava can leave a leading space in the second part. Use an exact comma-and-space delimiter, or clean the output if the source has inconsistent spacing.
  • Not leaving room for the results. SPLIT returns multiple cells, not one combined value. If a destination cell to the right is occupied, Sheets may show a #REF! error saying the array result could not expand. Move or clear the blocking content rather than deleting the source by accident.
  • Assuming the delimiter string always means an exact sequence. With the default split_by_each setting, every character in a multi-character delimiter is considered individually. Use FALSE as the third argument when you need the full sequence to act as one separator.
  • Using SPLIT when the separator is not consistent. If some records use commas and others use spaces, one simple formula may produce uneven columns. Standardize the source first or use the Data menu option on carefully selected data after checking how it detects separators.
  • Expecting the formula to remove unwanted punctuation or fix spelling. SPLIT divides text at delimiters; it does not automatically correct capitalization, typos, or stray symbols. Treat cleanup as a separate step and verify the results before using them elsewhere.

Tips

  • Test with a representative sample. Include a short value, a long value, and one with repeated or missing separators. A formula that works for one neat row may expose extra spaces or blank pieces in other records.
  • Keep the original column until you have checked the output. A formula-based split leaves the source intact, making it easier to compare each part and correct the delimiter. If you use Data > Split text to columns, make a copy first when the original values matter.
  • Use exact separators for multi-character patterns. A marker such as :: can be treated as one delimiter by setting split_by_each to FALSE. This prevents a split at each colon or space when you only want to divide at the complete marker.
  • Use the formula when source data may change. A SPLIT result recalculates when its referenced cell changes. The menu command creates separated cell values instead, which can be useful for a one-time conversion but does not maintain a live link to the original text.
  • Check the spreadsheet locale if a formula will not parse. Google Sheets may use different argument separators depending on locale settings. If the standard comma-separated formula is rejected, check the spreadsheet’s locale and use the argument separator expected by that sheet.
  • Remember the difference from Excel. Google Sheets uses SPLIT for this operation. Excel does not use this Google Sheets function; Microsoft 365 Excel offers TEXTSPLIT for formula-based splitting, with different syntax. Menu names and behavior can also differ between the two programs.

Troubleshooting

The formula returns the original text in one cell. Confirm that the delimiter you entered appears in the source value exactly as written. A hyphen and an en dash are different characters, and a regular space is different from a nonbreaking space. Copying the separator from the source can help. Also make sure the formula refers to the correct cell.

The result has extra blank columns or empty pieces. Repeated delimiters, such as two spaces in a row or consecutive commas, can create empty results. The fourth argument, remove_empty_text, is TRUE by default and removes empty results; setting it to FALSE preserves them. If spacing is accidental, correct the source or choose the arguments that match the structure you need. Do not assume empty items are meaningless if they represent missing fields in the original data.

There is a leading or trailing space in a result. This often happens when the delimiter handles punctuation but not the adjacent space, or when spaces vary between records. Use an exact delimiter that includes the space if it is consistent. If it is inconsistent, clean the source or trim the resulting text in a separate step, then inspect the full set for exceptions.

You see a #REF! error or only part of the result appears. Look across the destination row for existing values, merged cells, or other content that prevents the formula output from expanding. Clear or move the blocking cells, then let the formula recalculate. If you intend to split a long list, make sure there are enough columns available.

The formula shows a parse error. Check that the formula begins with an equals sign, that quotation marks around the delimiter are straight quotes, and that all parentheses are balanced. In some spreadsheet locales, formulas use semicolons rather than commas between arguments. Check the locale setting instead of changing characters randomly.

Frequently Asked Questions

Does SPLIT put results into rows or columns?
By default, it places the pieces in adjacent columns on the same row. Use TRANSPOSE around the result if you need the pieces to run vertically, and leave enough empty cells in the direction the results will fill.

Can I split a cell using more than one delimiter?
Yes. Put multiple single-character delimiters in the delimiter argument, such as a comma and semicolon, when each character should trigger a split. The default treats each character separately. If your intended delimiter is a multi-character sequence, set split_by_each to FALSE so Sheets matches the sequence as a whole.

Can I split text without a formula?
Yes. Select the cells and choose Data > Split text to columns, then choose the separator. That is convenient for a one-time change. Use the SPLIT formula when you want results that update if the source cell changes.

Why is there a space before one of the split results?
The space was not included in the delimiter. For consistently formatted text such as Patel, Ava, use comma-space as the exact delimiter and set split_by_each to FALSE. If records use different spacing, the exact-delimiter approach may not work uniformly.

Can SPLIT separate text into rows in the original list?
Not by itself: its normal output runs across columns. Wrap it in TRANSPOSE to orient one cell’s results vertically. If you are combining many records, check the required output layout first so results do not collide with other data.

Does SPLIT work in Microsoft Excel?
SPLIT is the Google Sheets function. In Microsoft 365 Excel, TEXTSPLIT is the corresponding formula option, but its arguments and syntax are not identical. Use instructions written for the spreadsheet program you have open.

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.