To split text in Google Sheets, select the cells, choose Data > Split text to columns, then select the separator that appears between the pieces. The contents will be divided across columns to the right, so first make sure those cells are empty. This is useful for separating full names, addresses, product codes, or other combined values without retyping them.
What You’ll Need
- A Google account and a spreadsheet open in Google Sheets in a web browser.
- The text you want to divide, arranged in a column. Splitting works best when each cell follows the same pattern, such as a first name, a comma, and a last name.
- Empty columns to the right of the source data for the separated results. If those columns contain information, move or copy that information elsewhere first.
- Optional: a copy of the original data, or a duplicate sheet, if you may need to restore the unsplit values.
Step-by-Step
- Check how the values are joined. Look at several rows, not just the first one, and identify the character or pattern between the pieces. For example,
Jordan Leeuses a space,Jordan,Leeuses a comma, andJordan | Leeuses a vertical bar. Check whether the separator is consistent and whether it also appears inside any piece of information. A comma in an address, for instance, may not always mark the boundary you want. - Make room for the results. Determine how many parts the split could produce and check that the columns immediately to the right of the selected data are blank. A value divided into three parts needs room for the original column plus two additional columns. Google Sheets places the pieces in neighboring cells on the same rows; existing content in the destination area may be replaced. When in doubt, insert blank columns or create a duplicate sheet before proceeding.
- Select the source cells. Click the first cell in the column, then drag down to select the data range. You can also click the column letter to select the entire column, but selecting only the populated range is usually tidier and reduces the chance of affecting unrelated data. If you select a header along with the records, it will be split too, so leave the header out if it should stay as one label. Avoid selecting multiple unrelated columns for a single split operation.
- Open the split tool. In the Google Sheets menu bar, choose Data > Split text to columns. A separator control appears near the selected cells. If the menu item is unavailable, check that the selection contains text and that you are editing a Google Sheets spreadsheet rather than viewing a read-only file or a different application.
- Choose the separator. Open the separator dropdown and choose the option that matches the text: Comma, Semicolon, Period, or Space. Choose Detect automatically when the data uses a clear, consistent delimiter and you are not sure which one it is. If the separator is another character, choose Custom and enter that character, such as a pipe symbol. Watch the cells as you change the option; Sheets previews the split so you can see whether the pieces land in the intended columns.
- Review the preview before accepting it. Check a few representative rows, including the longest or messiest values. Confirm that each piece has gone into the right column and that the split has not created too many columns. A space separator can split every space, not just the one between a first and last name. A comma separator can split commas inside addresses or descriptions as well as the intended comma. If the preview is wrong, choose a different separator or cancel and prepare the text before trying again.
- Finish and verify the results. Once the preview looks right, click a cell outside the selected range to close the control and inspect the output. Check the first row, a middle row, and the last row. Confirm that the destination cells contain the right values, that no original information was lost, and that the header remains clear. If the result is correct, you can add or adjust column headings, resize columns, and remove the original combined column only when you are sure it is no longer needed.
For example, suppose column A contains Item-Blue-XL and similar values, with hyphens separating the item, color, and size. Select the populated cells in column A, choose Data > Split text to columns, and set the separator to a custom hyphen. The results appear in columns A, B, and C on the same rows. If a hyphen can also occur within an item name, this simple split will divide that name too, so inspect the data and consider cleaning it first.
Common Mistakes
- Splitting on a character that appears inside the data. If a comma is used both as a separator and within an address, every comma may create another column. Check several examples before choosing the delimiter. When the data has no dependable boundary, splitting it automatically may not be reliable.
- Forgetting about occupied cells to the right. The new pieces need neighboring cells. Existing values in the output area can be overwritten, so check the full width needed before you run the command. Do not assume that Sheets will insert new columns automatically.
- Using Space for full names without checking for middle names. A two-part name may split neatly, while a name with a middle name or multiple surnames may produce three or more pieces. Decide whether you need every word separated or want to keep some name parts together.
- Including a heading unintentionally. A header such as
Full Namewill split at the space along with the records. Select the data rows only, or plan to repair the heading afterward. - Expecting a split to clean up the text. Extra spaces, inconsistent punctuation, and blank values can produce untidy results. The split tool divides text at the chosen delimiter; it does not determine what the data means or standardize every row.
Tips
- Preserve an untouched copy. Duplicate the sheet using the sheet tab menu or copy the source column to a spare area before splitting. This gives you a straightforward way to compare the separated values with the originals and recover if the split is not useful.
- Use a distinctive delimiter where possible. A character such as a vertical bar is less likely than a space to appear naturally in names or descriptions. If you control how data is exported or entered, a consistent delimiter makes later splitting simpler.
- Check the range width in advance. Count the maximum number of pieces in a representative row and make room for them. Even if most values have two parts, one row with four parts may extend the result farther than expected.
- Keep a simple header for each new column. Once the values are split, label the columns clearly, such as First name and Last name. If the split creates more fields than expected, use headings to record what each column contains before cleaning or rearranging the data.
- Use the browser version for these instructions. The menu path above is for Google Sheets in a web browser. In Excel, the comparable built-in feature is Text to Columns, found on the Data tab; its steps and interface differ. Do not follow the Google Sheets menu path in Excel.
Troubleshooting
The split option is missing or disabled. Click a cell or select a range containing the text you want to divide, then open Data again. If you only have view access, ask for editing permission. If the file is open in a preview or another spreadsheet program, open it in Google Sheets before following these steps.
Everything stays in one column. The selected separator may not match the actual character in the text. Try Detect automatically or choose Custom and enter the delimiter exactly as it appears. Watch for punctuation that looks similar but is different, such as a regular hyphen and a longer dash. If a value contains spaces around commas, the comma is still the separator; the extra spaces may remain at the edges of the resulting text.
Values split into too many pieces. The chosen character may appear within the information, or a space may be separating more than the two fields you intended. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then try a more specific delimiter or prepare a copy of the data. If different rows follow different patterns, correct those inconsistencies first instead of applying one delimiter to the entire range.
Some rows do not split like the others. Look for missing delimiters, extra delimiters, blank cells, and inconsistent spacing. Correct a few irregular source values, then select the relevant range and try again. For a large list, sorting or filtering the source column can help group unusual entries for review, but keep a backup before changing the original data.
Information to the right disappeared. The split likely used cells that already contained data. Undo at once if possible. If undo is no longer available, use version history or your backup to restore the earlier values. On the next attempt, insert enough empty columns before splitting or perform the operation on a duplicate sheet.
Frequently Asked Questions
Can I split a cell into separate columns in Google Sheets?
Yes. Select the cell or cells and use Data > Split text to columns. Choose the delimiter that separates the pieces, and Sheets places the results in adjacent columns on the same rows.
Can I split data using a character that is not listed?
Yes. In the separator dropdown, choose Custom and enter the character used in the text. Check the preview afterward, especially if that character might also appear inside a value.
Can I split a full name into first and last name?
Usually, if each name has exactly two parts separated by one space. If names include middle names, multiple surnames, or titles, splitting on every space can create extra columns. Review the range carefully and consider handling unusual names separately.
Does splitting change the original cells?
The first piece remains in the original column, and the other pieces go into columns to its right. Because this is an edit to the sheet, keep a copy first if you need to preserve the combined values exactly as they were.
Can I split data into rows instead of columns?
The Split text to columns command divides values across columns, not down into separate rows. If you need one item per row, you will need to rearrange the results after splitting or use a different method suited to the structure of your data.
Is this the same process in Excel?
No. These steps are for Google Sheets in a browser. Excel for Microsoft 365 on Windows has a separate Text to Columns tool on the Data tab, with its own prompts and options.
Related
- JavaScript split(): Syntax, Examples, and Common Mistakes
- Chemistry Balance Equation Worksheet: Practice and Answers
- Is the Balance of Payments Always Zero? Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add Page Numbers in ExcelTo add page numbers in Excel for Microsoft 365 on Windows, open the worksheet in Page Layout view,…
- Hanging Indent in Word: Format Reference Lists EasilyTo hang-indent references in Microsoft Word, select the reference paragraphs, open the Paragraph settings from the Home tab,…
- Excel Essential Keyboard Shortcuts for Everyday WorkFor Excel for Microsoft 365 on Windows, start with these essentials: Ctrl+C, Ctrl+V, and Ctrl+Z to copy, paste,…
- Protected Sheets in Google Sheets: Setup and PermissionsA protected sheet in Google Sheets limits which editors can change a whole sheet or selected cells. In…
- Conditional Formatting Color Scales in Excel ExplainedIn Excel for Microsoft 365 on Windows, a conditional formatting color scale colors cells according to their numeric…
- Excel Audit Spreadsheet Examples and How to Build ThemUseful Excel audit spreadsheet examples include an expense review log, a bank reconciliation, an inventory count comparison, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.