Split Text in Google Sheets: Methods, Steps, and Tips

To split text in Google Sheets, select the cells you want to separate, choose Data > Split text to columns, then pick the delimiter, such as a comma or space. For a formula-based split that updates when the source changes, use the SPLIT function. Both methods turn text stored together in one cell—such as a full name or a comma-separated list—into separate cells.

Before you start, check that the cells to the right of your source data are empty: the split results will fill them. If you need the original text for reference, make a copy of the column first. The menu method is convenient for a one-time cleanup; a formula is useful when you expect the source text to change or want to keep the original column intact.

What You’ll Need

A Google Sheets spreadsheet open in a web browser, with the text you want to split.
A clear idea of what separates the pieces, such as a comma, space, hyphen, semicolon, or another character.
Empty cells beside the source column for the results, or an empty area on another sheet.
Optional: a copy of the source data if you want to preserve the original or test the result safely.

Step-by-Step

Review the text and identify its separator. Look at several rows, not just the first one. For example, entries such as Lee, Jordan use a comma, while Lee Jordan use a space. Check whether every row follows the same pattern. A comma followed by a space is not always the same as a comma alone, especially if some rows have inconsistent spacing. Also note whether a character appears inside a value, such as a hyphen in a product name; splitting on that character could break the value in the wrong place.
Make room for the output. If the source text is in column A, check columns B, C, and any other columns the split might use. Move or copy existing content first if those cells are occupied, because splitting can replace values in the destination cells. To preserve your source, copy the column to an empty part of the sheet or use a spare sheet. For a formula, select an empty starting cell with enough blank space to its right for all the pieces.
Select the source cells. Click the first cell in the text column and drag down to select the range, or click the column letter if the entire column contains the text to split. Selecting just the cells with data makes it easier to avoid changing unrelated rows. If your selection includes a header, decide whether it should be split too; you can select the data rows only to leave the heading unchanged.
Open the split tool. In Google Sheets in a browser, choose Data > Split text to columns. A separator control appears near the selected cells. This is a built-in Sheets command, not a formula, so it puts the separated pieces into cells and does not leave a calculation in the source cell. If you do not see the command, confirm that you have selected cells containing text and that you are editing a Google Sheets file rather than viewing a read-only copy.
Choose the separator. Open the separator control and select the option that matches the text, such as Comma, Space, Semicolon, or Custom. You can also try Detect automatically when the separator is consistent and recognizable. For a character that is not listed, choose Custom and enter it. If the data uses a comma followed by a space as a single delimiter and you need precise handling, inspect the preview and try a custom separator. Watch the preview as you change the setting; it should show the intended pieces in separate columns.
Check the preview and the destination cells. Confirm that each piece lands in the right column and that the first row is not being split unexpectedly. If a name such as Jordan Lee becomes two columns, that may be correct; if a product such as Model A-12 breaks at the hyphen, choose a different separator. Check the cells to the right again before committing. The split output can overwrite existing contents, and Google Sheets does not necessarily ask you to confirm each overwritten cell.
Apply the split and inspect several rows. Once the preview looks right, click outside the separator control or otherwise finish the operation, then review the resulting columns. Check the top, middle, and bottom of the range for irregular rows, extra spaces, blank pieces, or missing values. If the result is wrong, use Edit > Undo immediately, then select the range again and choose a better separator. Avoid making several unrelated edits before undoing, because that makes it harder to restore only the split.
Use a formula when you need an updating result. With the original text in A2 and a comma between the parts, enter =SPLIT(A2,",") in an empty cell, such as B2. The pieces spill into cells to the right. For example, if A2 contains red,blue,green, the formula returns red in B2, blue in C2, and green in D2. To split every row, fill the formula down by dragging the small square at the lower-right corner of B2, or copy and paste it into the rows below. Keep the spill area clear. Unlike the menu command, the formula result recalculates if A2 changes.
Set formula behavior for a multi-character delimiter if needed. By default, SPLIT treats each character in its delimiter argument separately. If the text is Lee, Jordan and you want the exact comma-and-space sequence to act as one delimiter, put the text in A2 and enter =SPLIT(A2,", ",FALSE,TRUE) in B2. The pieces return as Lee and Jordan, without an extra leading space. The third argument, FALSE, tells Sheets to treat the delimiter string as one unit; the fourth argument, TRUE, removes empty results. With a simple one-character delimiter, the shorter formula is usually sufficient.

The menu route is generally best for cleaning a fixed batch of data: its results are ordinary cell values and do not depend on the source afterward. The formula route is best when the source will keep changing, when you want a repeatable result, or when you want to keep the source column untouched. If you are moving from Excel, note that the instructions here are for Google Sheets; Excel has a separate Text to Columns command, and Google Sheets’ SPLIT function is not the same as Excel’s splitting tools.

Common Mistakes

Splitting on the wrong character: Choosing Space for full names can separate a middle name into an extra column. Choosing a hyphen can break product codes. Check multiple examples before applying the split.
Overwriting cells beside the source: A split can fill several columns, not just the next one. Clear or move anything that occupies the likely output area before running it.
Assuming every row has the same structure: If some records contain two commas and others contain four, the output columns may not line up neatly. Review exceptions and decide whether to clean the data first.
Forgetting that spaces matter: A comma delimiter does not automatically remove spaces after commas in every setup. If a formula leaves leading spaces, use a delimiter that includes the space with the appropriate SPLIT settings, or clean the output separately.
Splitting a value that includes the delimiter: A comma inside a quoted address or description may be part of the value rather than a boundary. The basic split tool and SPLIT function do not interpret quoted CSV fields as a full CSV parser would. Use a proper import or data-cleaning method for complex quoted records.
Expecting split results to stay linked: The menu command produces cell values. If you later edit the original text, those values do not automatically update. Use a formula when recalculation is important.

Tips

Test on a few rows first. Copy a small sample to a blank area and try the separator there. This is a quick way to catch inconsistent punctuation before applying a change to hundreds of records.
Keep a backup of important source data. Duplicate the column or make a copy of the sheet before a large one-time cleanup. This also gives you a straightforward way to compare the original text with the split output.
Use the formula method for a repeatable workflow. If new rows arrive regularly, keep the source in one column and place formulas in neighboring columns. Confirm that the destination cells are clear so the formula can expand into them.
Check leading zeros and date-like text. Codes such as 00125 can be mistaken for numbers, and strings that resemble dates may be displayed as dates. Inspect the result after splitting. If a value must remain text, format the destination cells as plain text before entering or importing the values, and verify the displayed result.
Use a consistent delimiter when collecting data. If you control the source, settle on one separator and use it consistently. For example, choose a comma or a tab rather than mixing commas, slashes, and spaces across rows. Consistent input makes both the menu tool and formulas more reliable.

Troubleshooting

Nothing appears to happen: Check that the cells you selected contain the text, then reopen Data > Split text to columns. Look for the separator control near the range; the results may not be obvious if the cells to the right were already empty or the selected separator does not occur in the text. Try Custom with the exact character you can see in a source cell.

Everything lands in one column: The selected separator may not match the actual text. Check for a different punctuation mark, an extra space, or a tab. Select a cell and inspect its contents in the formula bar, then choose the matching separator. If separators vary between rows, standardize the source first or handle the exceptional rows separately.

Some rows split into too many pieces: The delimiter may also appear inside the values. For example, splitting an address on commas can separate street, city, and other components in addition to the parts you wanted. Undo the operation, choose a delimiter that uniquely marks the boundary, or use a more suitable data-import process if the text contains quoted fields.

The formula shows an error or will not expand: Check that the formula begins with =SPLIT, that the source reference is correct, and that the delimiter is inside quotation marks. If an array or spill error appears, clear cells to the right of the formula so the results have room. If the source cell is blank, the output may also appear blank or behave differently from a populated example; test with a filled source cell.

There are extra blank columns or unexpected gaps: Repeated delimiters can create empty pieces, such as in red,,green. With a formula, use the fourth SPLIT argument to remove empty text, as in the multi-character example above. For the menu method, clean repeated separators in the source or review the output and remove unnecessary empty columns.

Numbers or dates look different after splitting: Sheets may interpret values as numbers or dates rather than display them as literal text. Check the actual cell value and its number format. If the content is an identifier that must retain leading zeros, set the destination format to plain text before splitting or importing, then confirm the zeros remain.

Frequently Asked Questions

Can I split text into rows instead of columns?
Data > Split text to columns separates pieces horizontally into neighboring columns. To place pieces vertically in rows, use a formula or rearrange the results after splitting; the menu command is designed for columns.

Will splitting change or delete my original text?
The menu command uses the selected cells as the starting point and writes results across the row, so the original cell contents are replaced by the first piece. Copy the source first if you need to keep it. A formula in another column leaves the source cell unchanged.

Can I split text by more than one delimiter?
Yes. With the menu, choose a separator that matches the data or use Custom for a specific character. With SPLIT, the delimiter argument can contain multiple characters; by default Sheets treats them individually, while the third argument set to FALSE treats the delimiter string as one sequence.

How do I split a full name into first and last names?
If the names consistently have one space between two parts, select the name cells and choose Data > Split text to columns, then choose Space. Names with middle names, multiple spaces, or compound surnames need extra review because a space does not always mark the first-and-last boundary.

Can I undo a split?
Yes. Immediately choose Edit > Undo or press Ctrl+Z on Windows or ChromeOS. On a Mac, use Command+Z. If you have made other changes since splitting, undo may reverse those changes too, so keeping a copy beforehand is safer for important data.

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.