How to Split and Separate Data in Google Sheets Columns Automatically

You've imported data from a CSV, received a form export, or inherited a spreadsheet where names, addresses, or product details are crammed into a single column. "John Doe, john@email.com, New York" all in one cell — sound familiar?

Splitting combined data into separate columns is one of the most common data cleanup tasks in Google Sheets. In this guide, you'll learn 4 methods to split text into columns, plus how to clean up the messy aftermath automatically.

The Problem: Data Crammed into One Column

Combined data in a single column causes serious issues for spreadsheet workflows:

• Broken sorting: You can't sort by last name if "John Doe" is in one cell.
• No filtering: Filtering by city is impossible when it's combined with other data.
• Formula failures: VLOOKUP and MATCH can't find individual values inside combined strings.
• Mail merge issues: Personalized emails need first name, last name, and email in separate columns.
• Reporting gaps: Pivot tables and charts can't group data that's stuck in a single column.

Method 1: Split Text to Columns (Built-in Feature)

Google Sheets has a built-in "Split text to columns" feature that works great for simple, consistent delimiters.

Steps:

1. Select the column or cells containing combined data.
2. Go to Data → Split text to columns.
3. A small menu appears at the bottom of the screen.
4. Choose your delimiter:
   • Comma — for CSV-style data ("John,Doe,NY")
   • Semicolon — for European CSV formats
   • Period — for IP addresses or version numbers
   • Space — for "First Last" name splitting
   • Custom — enter any character (e.g., "|" or "-")
5. Data is instantly split into adjacent columns.

Best for: Quick one-time splits with consistent delimiters.
Limitation: Destructive — overwrites the original data. Can't handle mixed delimiters. No undo once columns are overwritten.

Method 2: SPLIT Formula (Non-Destructive)

The SPLIT function splits text by a delimiter and returns results in separate cells — without modifying the original data.

Syntax:

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

Examples:

// Split by comma
=SPLIT(A2, ",")
// "John,Doe,NY" → John | Doe | NY

// Split by space (for names)
=SPLIT(A2, " ")
// "John Doe" → John | Doe

// Split by multiple delimiters
=SPLIT(A2, ",;", TRUE)
// "John,Doe;NY" → John | Doe | NY

// Keep empty segments
=SPLIT(A2, ",", TRUE, FALSE)
// "John,,NY" → John | (empty) | NY

Handling Edge Cases:

• Names with middle names: =SPLIT("John Michael Doe", " ") creates 3 columns. Use INDEX to grab specific parts.
• Trailing delimiters: "John,Doe," creates an empty cell at the end. Set the last parameter to FALSE to preserve it.
• Array formula for entire columns: =ARRAYFORMULA(IF(A2:A<>"", SPLIT(A2:A, ","), )) — note that SPLIT doesn't natively work in ARRAYFORMULA in all cases. Consider using the built-in feature or Apps Script for bulk operations.

Best for: Non-destructive splitting where you want to keep the original data intact.
Limitation: Creates formula dependencies. Doesn't work well with inconsistent delimiters within the same column.

Method 3: REGEXEXTRACT for Complex Patterns

When data doesn't follow a simple delimiter pattern, REGEXEXTRACT uses regular expressions to pull out specific pieces of information.

Common Patterns:

// Extract email from mixed text
=REGEXEXTRACT(A2, "[\w.-]+@[\w.-]+\.\w+")
// "Contact: john@example.com (Sales)" → john@example.com

// Extract phone number
=REGEXEXTRACT(A2, "\d{3}[-.]?\d{3}[-.]?\d{4}")
// "Call 555-123-4567 for info" → 555-123-4567

// Extract first word (first name)
=REGEXEXTRACT(A2, "^\w+")
// "John Doe" → John

// Extract text between parentheses
=REGEXEXTRACT(A2, "\(([^)]+)\)")
// "Product A (Category: Electronics)" → Category: Electronics

// Extract ZIP code
=REGEXEXTRACT(A2, "\b\d{5}(?:-\d{4})?\b")
// "123 Main St, Springfield, IL 62704" → 62704

Best for: Extracting specific data types (emails, phone numbers, codes) from unstructured text.
Limitation: Steep learning curve. Regex patterns break easily with inconsistent formatting.

Method 4: Apps Script for Bulk Splitting

For large-scale data splitting that needs to run automatically, use Apps Script.

Steps:

1. Open your spreadsheet.
2. Go to Extensions → Apps Script.
3. Paste the following script:

function splitColumnData() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A2:A" + sheet.getLastRow());
  var values = range.getValues();
  var delimiter = ","; // Change to your delimiter
  var output = [];

  for (var i = 0; i < values.length; i++) {
    var cell = values[i][0].toString();
    var parts = cell.split(delimiter).map(function(s) {
      return s.trim();
    });
    output.push(parts);
  }

  // Find max columns needed
  var maxCols = Math.max.apply(null, output.map(function(row) {
    return row.length;
  }));

  // Pad rows with fewer columns
  output = output.map(function(row) {
    while (row.length < maxCols) row.push("");
    return row;
  });

  // Write to columns B onwards
  sheet.getRange(2, 2, output.length, maxCols).setValues(output);

  SpreadsheetApp.getUi().alert(
    'Done! Split ' + output.length + ' rows into ' + maxCols + ' columns.'
  );
}

4. Click Run and authorize the script.
5. The script splits column A data by comma and writes results starting from column B.

Best for: One-time bulk splits on large datasets (10,000+ rows).
Limitation: Requires coding. Overwrites columns B onwards. No preview.

Post-Split Cleanup with CleanSheet

After splitting data, you'll often end up with messy results — empty cells, extra whitespace, blank rows from missing data, and inconsistent formatting. This is where CleanSheet comes in.

Common post-split cleanup tasks:

1. Remove empty rows: Splits often create rows where some columns are blank. Use CleanSheet's "Empty Row" rule to delete them.
2. Trim whitespace: Split data often has leading/trailing spaces. CleanSheet can trim all whitespace automatically.
3. Delete the original column: After verifying the split, remove the combined data column to avoid confusion.
4. Schedule ongoing cleanup: If new combined data arrives regularly (e.g., from form submissions), set up a scheduled CleanSheet rule to keep things clean.

Method Comparison

Frequently Asked Questions

How do I split text to columns in Google Sheets?

Select the cells with combined data, go to Data → Split text to columns, and choose your delimiter (comma, space, semicolon, or custom). Google Sheets will split the content into adjacent columns automatically. For a non-destructive approach, use the =SPLIT() formula instead.

Can I split data and clean up the original column automatically?

Yes! After splitting data, use CleanSheet to automatically clean up empty rows, trim whitespace, and remove the original combined column. Set up scheduled rules to keep your split data clean going forward.

What is the SPLIT function in Google Sheets?

The SPLIT function divides text by a specified delimiter and returns each segment in separate cells. Syntax: =SPLIT(text, delimiter, [split_by_each], [remove_empty_text]). For example, =SPLIT("John,Doe,25", ",") returns "John", "Doe", and "25" in three adjacent cells.

How do I split a full name into first and last name?

Use =SPLIT(A2, " ") to split by space. For names with middle names, combine with INDEX: =INDEX(SPLIT(A2, " "), 1, 1) for the first name and =INDEX(SPLIT(A2, " "), 1, 2) for the last name. Be aware that names with multiple spaces (e.g., "Mary Jane Watson") will produce more than two columns.

Related Articles

• How to Automatically Clean Capitalization and Spaces in Google Sheets
• How to Remove Special Characters and Clean Text in Google Sheets
• How to Consolidate and Merge Multiple Google Sheets Tabs into One