How to Separate Text in Google Sheets Using Delimiters

To separate text in Google Sheets, select the cells, choose Data > Split text to columns, then select the character that divides the text. For a repeatable result that updates when the source changes, use the SPLIT function instead. Either approach can turn entries such as Maya Chen, Seattle, WA into separate columns for a name, city, and state. Before you start, check that the cells to the right of your data are empty so the separated values have room to appear.

What You’ll Need

A Google Sheets file open in a web browser, with the text you want to separate.
A delimiter: the character or string between the pieces, such as a comma, space, hyphen, tab, or vertical bar.
Empty cells to the right of the selected data, or a spare area where you can test the split without affecting other information.

Step-by-Step

Check a few source cells and identify the separator. Look at several rows, not just the first one. In Maya Chen, Seattle, WA, commas separate the three fields, but a space occurs inside the full name too. A comma is therefore the right delimiter if you want to keep the first and last name together. If the entries use a pattern like Maya Chen | Seattle | WA, the vertical bar is the delimiter instead. Decide whether spaces around the delimiter should be removed or kept.
Make room for the results. Splitting one cell into three parts requires the original column plus two empty columns beside it. If your data is in column A, inspect columns B and C for existing values, formulas, or notes. To protect the source, make a copy of the sheet tab or paste the source column into an unused area before splitting. This is especially useful when the data came from an import and you are not sure how many parts each row will produce.
Select the cells to split. Click the first cell and drag through the range, or click the column letter if you want to select the entire column. Selecting only the filled range, such as A2:A100, helps avoid splitting headers or unrelated cells. If the first row contains labels like “Full name, City, State,” decide whether those labels should be split too. You can leave the header out of the selected range and type suitable column headings above the results.
Open the split command. In the Google Sheets menu bar, choose Data > Split text to columns. Sheets will place the separated pieces into neighboring columns. A separator control appears near the selected data; it may initially show an automatically detected delimiter. If the automatic choice has separated the text incorrectly, open the control and choose the correct separator rather than accepting the first result.
Choose the delimiter. Select a listed option such as comma, semicolon, period, or space, or choose Custom and enter the exact character or string that appears between fields. For entries separated by a comma followed by a space, choosing comma usually works, but it can leave a leading space at the start of later pieces. If you need an exact multi-character separator, use a formula with the full string as its delimiter, as described below. Watch the live result in the sheet and confirm that each part lands in the intended column.
Review the split before continuing. Check several rows, including one with a longer value or an unusual entry. Confirm that names, addresses, product codes, or other fields have not been broken at an internal space or punctuation mark. If the separator choice was wrong, use Undo immediately or restore the copied source, then run the command again with the right delimiter. Do not sort or edit the split results until you have checked that the rows still correspond to the original records.
Use a formula when you want a dynamic split. A formula is useful when the source text may change or when you need to keep the original column untouched. With the text Maya Chen, Seattle, WA in A2, enter =SPLIT(A2,",") in B2. The result fills cells B2, C2, and D2 with Maya Chen, Seattle, and WA. The spaces after the commas remain because the delimiter is only a comma.
Remove extra spaces if needed. If you want the formula results cleaned up, and A2 contains Maya Chen, Seattle, WA, enter =ARRAYFORMULA(TRIM(SPLIT(A2,","))) in B2. It returns Maya Chen, Seattle, and WA across the row, trimming the leading spaces. Keep the cells to the right of B2 empty so the result can expand. If your data has no spaces around commas, the simpler SPLIT formula is enough.
Fill the formula down for more rows. If A2, A3, and later cells each contain one record, put the formula in B2 and copy it down using the small fill handle at the corner of the selected cell. Each row’s result will spill across neighboring columns. Make sure the spill area is clear on every row. For a one-time conversion, the menu command may be quicker; for data that gets refreshed, formulas are easier to recalculate. If you need static output from a formula, copy the results and use Edit > Paste special > Values only in the destination area.

Google Sheets formulas and menu labels can vary slightly with account language or regional settings. The instructions above describe Google Sheets in a web browser. In Excel, the comparable built-in tool is usually Data > Text to Columns; the menu path and dialog are different.

Common Mistakes

Choosing a character that appears inside the data. Splitting a full name on every space will turn a two-word name into separate cells. Use a separator that divides fields, not merely a character that happens to occur in the text.
Forgetting the output needs space. Neighboring cells may contain values or formulas you need. Review the destination area before using the menu or entering a formula, and test in a spare range if there is any doubt.
Leaving spaces around a delimiter. A comma split can leave leading spaces in the second and later fields. Use an exact delimiter such as comma-plus-space when appropriate, or trim the resulting pieces.
Assuming every row has the same structure. One record may have an extra comma, missing field, or separator inside a quoted address. Review exceptional rows individually rather than assuming the whole column is uniform.
Expecting the menu split to update automatically. The menu command creates values from the current text. If a source entry changes later, run the split again. A formula can recalculate from changed source text, provided the output area remains clear.

Tips

Keep an untouched copy of imported data until you have confirmed the split. This gives you a reliable reference if the delimiter or field order needs correcting.
Use a clear header for every resulting column, such as Name, City, and State. Labels make the split easier to filter, sort, and share with other people.
For a separator made of several characters, make the formula’s delimiter match the entire string. For example, with Chen||Seattle||WA in A2, enter =SPLIT(A2,"||",FALSE) in B2. The FALSE tells Sheets to treat the two vertical bars as one delimiter rather than splitting at each bar separately. The result is Chen, Seattle, and WA.
Use the menu for a quick, one-time cleanup and a formula when the source is likely to change. You can also copy formula results and paste values only when you want a fixed version to share.
Try the split on a small sample before applying it to a long column. A few representative rows can reveal inconsistent spacing, missing fields, or a delimiter that also appears inside a value.

Troubleshooting

The split appears in the wrong columns: Undo the operation, check which character truly divides the fields, and select the correct separator. If automatic detection picked the wrong one, choose a different option from the separator control.
The split does not happen or a formula shows an error: Check for occupied cells in the output range. A formula that returns several values needs empty neighboring cells; remove or move anything blocking the results. Also check that the cell reference in the formula matches the row containing your source text.
Some results begin with a space: The delimiter excluded the space after the comma. Use TRIM with SPLIT, or use the full comma-and-space string as the formula delimiter. Inspect the result before applying any additional cleanup.
Some rows produce fewer or more parts: Look for missing separators, extra separators, or a separator embedded within a field. Correct those source rows or handle them separately. A single split rule cannot reliably infer a missing field or distinguish an internal comma from a field boundary in every kind of data.
The formula shows its text instead of a result: Check that the cell is not formatted as plain text, and make sure the entry begins with an equals sign. Re-enter the formula in a normally formatted cell. If your spreadsheet’s locale uses semicolons between formula arguments, replace the commas that separate arguments with semicolons; do not change commas that are inside quoted text.

Frequently Asked Questions

Can I split text into columns without changing the original?
Yes. Put a SPLIT formula in an empty area or a new sheet, or duplicate the source sheet before using Data > Split text to columns. The formula approach leaves the source cells in place.

Can Google Sheets split text on a space?
Yes. Select Space as the separator in the split control. Be careful with names and addresses that contain internal spaces; splitting on a space will separate each word rather than preserve the whole field.

Can I split text using more than one character?
Yes. In the menu, choose Custom and enter the separator you need. With SPLIT, put the full delimiter in quotation marks and use FALSE as the third argument when Sheets should treat the string as one delimiter, as in the double-bar example above.

How do I split a full name into first and last names?
If every name has exactly two words, split on a space. If names can contain middle names, multiple surnames, or extra spaces, a simple space split may not identify first and last names reliably. Review those records or use a consistent source format before dividing them.

Will a split formula update when I edit the original text?
Yes. A formula such as SPLIT recalculates when its referenced source cell changes. The menu-based split is a one-time operation, so you need to run it again if the original text changes.

Why do extra values appear after splitting?
The chosen delimiter may occur within the text, or a row may contain more separators than the others. Check that row’s source value and decide whether the extra separator represents a real field boundary. If not, correct the source or use a more specific delimiter.

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.