Split Cell in Google Sheets: Separate Text Into Columns

To split text in a Google Sheets cell, select the cell or range, choose Data > Split text to columns, then pick the separator that divides the values. This puts each part into its own neighboring cell; it does not divide one cell into smaller pieces. Before you start, check that the cells to the right are empty so the split results do not replace data you want to keep.

What You’ll Need

A Google account and a spreadsheet open in Google Sheets in a web browser.
Text in one cell, or a range of cells, with a consistent separator such as a comma, space, semicolon, or hyphen.
Enough empty columns to the right of the selected data for every resulting part.
Optional: a spare copy of the data if it is important or difficult to recreate. You can duplicate a sheet by clicking the arrow on its tab and choosing Duplicate.

Step-by-Step

Review how the text is arranged. Click a cell and look at its full contents in the formula bar. Identify the character or pattern between the pieces you want to separate. For example, Mina Patel, Seattle, WA uses commas and spaces between a name and location fields. If some rows use commas while others use semicolons, decide whether to standardize the data first; one split setting may not handle inconsistent separators as intended.
Make room for the results. Count the number of pieces you expect from the longest entry. If a cell contains a first name, last name, city, and state, plan for four output columns. Inspect those columns on every selected row and move or copy any existing content elsewhere. The split operation places results in cells to the right, so data already there can be overwritten.
Select the source cell or range. Click one cell to split just that entry. To split multiple rows using the same separator, select the full source range, such as A2:A30. Do not select the whole sheet unless that is genuinely what you intend to process. If your data has a header, exclude it unless you also want the header text split.
Open the split command. In the Google Sheets menu bar, click Data > Split text to columns. A small separator control appears near the selected data. If the command is unavailable, check that a cell range is selected and that you are editing the sheet in Google Sheets rather than viewing a read-only preview.
Choose the separator. Open the separator control and choose the character that appears between the parts. Common choices include comma, semicolon, period, and space. Choose Custom when the separator is a character not listed, such as a pipe symbol (|) or a slash. If you choose automatic detection, check the result carefully: it may not infer the intended delimiter in data with mixed punctuation.
Check the output before continuing. Look across the new columns and compare a few rows with the original text. Confirm that names, numbers, punctuation, and leading or trailing spaces have landed where expected. A delimiter that appears inside a value—for example, a comma in a company name—can split that value too. If the result is wrong, use Undo immediately, then repeat the command with a better separator or a smaller selection.
Clean up and label the columns. Add clear headings above the separated fields, such as First name, Last name, and City. Check for empty output cells and inconsistent rows. If you need to retain the original combined text, keep it in its original column or make a copy before splitting. Once you have verified the result, you can adjust column widths and continue working with the separated data.

Example: If A2 contains Mina Patel, Seattle, WA, select A2 and choose Data > Split text to columns. Choose comma as the separator. The results occupy neighboring cells: Mina Patel, Seattle, and WA. Because each comma is followed by a space, the results may include a leading space after the first split, depending on the data and the split behavior. Check the output and remove unwanted spaces if necessary. For consistently formatted records, a comma followed by a space can be selected as a custom separator instead.

You can also split text with a formula if you want the result to update when the original changes. With the source text in A2 and the exact text Mina Patel, Seattle, WA, enter =SPLIT(A2,", ") in an empty cell. The formula returns Mina Patel, Seattle, and WA across neighboring cells. Keep those output cells clear so the formula can expand. SPLIT is a Google Sheets function; it is not the same as the Data menu command, and formula results remain linked to the source rather than becoming fixed values.

Common Mistakes

Confusing splitting text with splitting a cell. Google Sheets separates the contents into adjacent cells. It does not create two independently editable halves inside one cell. If you mean a merged cell, select it and use Format > Merge cells > Unmerge to restore its component cells; unmerging is different from splitting text.
Leaving important data in the output area. Results may replace cells to the right of the source. Check the entire selected range and all likely output columns first, not just the first row. Copy valuable data to a safe location before running the split.
Picking the wrong separator. Choosing a space can split a full name into several words; choosing a comma may split an address at every comma. Select the character that marks the boundary you actually want, and inspect entries that contain that character as part of their value.
Applying one separator to inconsistent rows. Some entries may use a comma, others a slash, and others no separator at all. The output can then have different numbers of pieces in different rows. Standardize the source or handle the exceptional rows separately.
Expecting the original contents to remain in the source cell. The split command is an editing operation, not a preview. Make a copy first if you need to preserve the combined value as well as the separated fields.

Tips

Test a small sample. If the sheet contains many rows or valuable records, copy a few representative rows to a temporary area and try the separator there. Include an ordinary row and an unusual one, such as a name with a middle initial or an address with extra punctuation.
Use a distinctive separator when possible. A pipe or a unique symbol is less likely than a space or comma to appear naturally inside a value. If you control how the data is exported, choosing a consistent delimiter makes future splitting more reliable.
Keep formulas separate from source data. Put a SPLIT formula in an empty area or a new sheet so its output can expand. If you want fixed results later, copy the formula output and use Edit > Paste special > Values only in the destination.
Preserve a clean source. Duplicate the sheet or copy the source column before making a large change. A backup is especially useful when the source has irregular punctuation, blank rows, or values that may be difficult to reconstruct.
Use the right tool for the job. For one-time separation of existing entries, Data > Split text to columns is usually quickest. A formula is useful when the source may change and you want the separated result to recalculate automatically.

In Microsoft Excel, the equivalent one-time feature is generally on the Data tab as Text to Columns. The menu names and steps are not identical to Google Sheets, so follow the instructions for the program you are using.

Troubleshooting

The split command does not appear: Confirm that you selected one or more cells containing text, and that you have permission to edit the sheet. If the file is open in view-only mode, request edit access or make an editable copy if allowed. In a browser, reload the sheet if its menus appear stuck, then select the cells again.

Everything stays in one column: The chosen separator may not exactly match the text. Check for a different character, such as a semicolon instead of a comma, or for a separator containing more than one character. Open the separator control and try the exact delimiter as a custom separator. Watch for nonstandard spaces copied from a website or document.

Values split into too many columns: The chosen character may also occur within the values. Undo the operation, choose a more specific delimiter, or split a smaller group of rows with a different setting. For example, splitting a full address on every comma may separate street details as well as city and state.

Some rows have blanks or shifted results: The source entries may not follow the same pattern. Compare a successful row with one that failed, looking for missing separators, repeated separators, or extra spaces. Separate the unusual rows independently or clean up the source format before trying again.

A formula gives an error or does not expand: Check that the source cell reference is correct and that the formula uses straight quotation marks around the separator. Make sure the cells to the right of the formula are empty; existing content can block an array of split results. If you see a formula parse error, check the commas and parentheses, then confirm that you are entering the formula in Google Sheets.

The result is not what you wanted: Use Undo promptly to restore the prior sheet, then check the selected range and separator before repeating the process. If you have already made other edits, avoid repeatedly undoing without checking what each step will reverse. Restore your saved copy if the original data has been lost and cannot be recovered through Undo.

Frequently Asked Questions

Can I split one Google Sheets cell into two cells?
Yes. Use Data > Split text to columns and choose the separator between the two pieces. The contents are placed in neighboring cells; the original cell is not divided into smaller on-screen sections.

Can I split a cell diagonally or divide its layout?
No. Google Sheets does not provide a feature to divide one cell into two independently editable cells while keeping the same grid position. You can unmerge a merged cell with Format > Merge cells > Unmerge, or place the pieces in adjacent cells.

How do I split a full name into first and last names?
Select the name cells and choose Data > Split text to columns, then select space. This works best when every name has exactly two parts. Names with middle names, compound surnames, or titles may need individual review because a space separator can produce more than two columns.

Can I split text using a custom symbol?
Yes. Open the separator control that appears after choosing Data > Split text to columns, choose Custom, and enter the symbol that separates the values. Check a sample first to ensure that the symbol does not also appear inside the data.

Will a formula update when I change the original cell?
Yes. A SPLIT formula recalculates from its source cell, provided the formula and its output cells remain in place. The menu command creates separated cell contents instead; it does not keep them linked to the original text.

Can I split multiple rows at once?
Yes, select the range of source cells and run the split command once when the rows use the same separator. Check that every row has enough empty space to the right and review irregular entries, since one separator setting may not suit every row.

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.