How to Split Text into Columns in Google Sheets (Menu Tool & SPLIT Formula)

Published on | ProSheetHub

When importing data from CSV exports, CRM systems, or online forms, information frequently arrives lumped together into a single cell—such as "John, Doe, Marketing, New York" or full names like "Jane Smith". Manually copying and pasting each word into its own column takes hours and introduces typos.

Google Sheets provides two fast solutions: the native Split text to columns menu tool (for instant one-time conversions) and the dynamic SPLIT formula (for automatic, live separation). In this tutorial, you will learn how to use both methods, handle custom delimiters, and split text into separate columns automatically.

1. The Quickest Way: The "Split Text to Columns" Tool

If you have existing static data that just needs immediate separation, use the built-in menu feature.

Step-by-Step Instructions:

  1. Highlight the column containing the combined text (e.g., Column A).
  2. Click Data in the top menu bar.
  3. Select Split text to columns.
  4. A small separator control box will appear at the bottom of your selection.
  5. Click the Separator dropdown and choose your delimiter:
    • Detect automatically (recommended for standard CSVs)
    • Comma
    • Semicolon
    • Period
    • Space
    • Custom (enter hyphens, slashes, or pipes like |)
Warning: The menu tool overwrites cells to the right. Always insert enough empty blank columns to the right of your data before splitting to avoid erasing existing information.

2. The Dynamic Method: Using the SPLIT Formula

If new data enters your spreadsheet regularly through forms or team entries, the menu tool won't update new rows. Instead, use the dynamic SPLIT function.

Formula Syntax:

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
  • text: The cell containing the combined string (e.g., A2).
  • delimiter: The character where the split should happen, wrapped in quotes (e.g., "," or " ").
  • split_by_each: Optional (TRUE by default). When TRUE, treats each character in your delimiter separately.
  • remove_empty_text: Optional (TRUE by default). Automatically removes consecutive repeated delimiters.

Example 1: Splitting First and Last Names

If cell A2 contains "Robert Downey", enter this formula in cell B2:

=SPLIT(A2, " ")

Google Sheets places Robert in cell B2 and automatically spills Downey into cell C2.

Example 2: Splitting an Email into Username and Domain

If cell A2 contains user@example.com, split by the @ symbol:

=SPLIT(A2, "@")

3. How to Split Entire Columns Automatically (ARRAYFORMULA + SPLIT)

Instead of dragging down the formula across hundreds of rows, combine SPLIT with ARRAYFORMULA or MAP to process the full dataset automatically:

=MAP(A2:A, LAMBDA(row, IF(row="", "", SPLIT(row, ","))))

Whenever a new entry is added to Column A, the split values instantly populate into the adjacent columns without manual intervention.

4. Method Comparison: Menu Tool vs. SPLIT Formula

Feature Split Text to Columns Tool SPLIT Function
Updates with new rows? No (One-time static action) Yes (Dynamic live recalculation)
Preserves original text? No (Replaces source column) Yes (Keeps raw column intact)
Best used for Quick CSV cleanup before analysis Automated dashboards, form entries, & templates

Frequently Asked Questions

Why does my SPLIT formula show a #REF! error?

A #REF! error usually means there is already text or numbers in the cells to the right. Clear out any content in adjacent cells so the formula has enough room to spill the split items.

Can I split by multiple different characters at once?

Yes. If your text contains both commas and hyphens (e.g., "Item1, Red-Large"), pass both into the delimiter argument: =SPLIT(A2, ",-"). Google Sheets splits at both characters automatically.

Recommended Next Steps

Properly separating raw text strings into structured columns makes reporting, sorting, and math formulas far simpler. Check out these related spreadsheet guides:

Comments

Post a Comment