How to Split Text into Columns in Google Sheets Using Delimiters

When importing data from CSV exports, copying contact lists from emails, or cleaning survey responses in Google Sheets, you frequently encounter combined data trapped inside a single cell—such as “Firstname Lastname”, “City, State, Postcode”, or product codes containing hyphens. Manually retyping data into separate fields is laborious. Google Sheets offers two powerful solutions to separate combined strings into distinct columns: the one-click Split text to columns menu tool and the dynamic SPLIT mathematical formula.

Method 1: Using the Built-In ‘Split Text to Columns’ Tool

For quick one-off data cleanup without formulas:

1. Open your spreadsheet in Google Sheets.
2. Highlight the column containing the combined text (e.g., column A containing full names).
3. Ensure you have empty adjacent columns to the right; splitting data will overwrite cells directly to the right of your selection.
4. Click on the Data menu in the top toolbar.
5. Select Split text to columns.
6. A small separator control box will appear at the bottom-right of your highlighted column.
7. Click the Separator dropdown and choose your delimiter:
   • Detect automatically: Google Sheets examines the text and chooses commas, spaces, or tabs.
   • Comma, Semicolon, Period, Space: Standard common separators.
   • Custom: Type any specific character (e.g., a hyphen -, pipe |, or slash /).
8. Your text splits instantly across adjacent columns.

Method 2: Using the Dynamic SPLIT Formula

If you want incoming form data or live-synced spreadsheets to split automatically without manual menu clicks:

1. Click on an empty target cell where you want the split data to begin (e.g., cell B2).
2. Type the following formula syntax:

   =SPLIT(A2, " ")

3. In this example, A2 is the source cell and " " (space) is the delimiter.
4. Press Enter. Google Sheets automatically populates cell B2 with the first name and spills cell C2 with the surname.

Splitting by Multiple Delimiters Simultaneously

If your text contains irregular separators (such as commas followed by spaces, or both hyphens and slashes):

=SPLIT(A2, ", ", TRUE, TRUE)

The third argument (split_by_each) set to TRUE treats each character in your delimiter string as an independent separator, perfectly parsing complex address lines or mixed product serial numbers.

Common Troubleshooting: Avoiding the #REF! Overwrite Error

When using the SPLIT formula, ensure that the cells directly to the right are completely empty. If an existing word, number, or note occupies cell C2 when formula =SPLIT(A2, " ") attempts to spill, Google Sheets displays a #REF! error reading: “Array result was not expanded because it would overwrite data in C2.” Delete the blocking content to allow the formula to expand.