How to Split Text to Columns in Google Sheets
Learn how to split text into separate columns in Google Sheets using SPLIT and TEXTSPLIT, with practical examples, troubleshooting tips, and ready-to-use templates to boost your workflow.
By the end of this guide, you will be able to split a single cell’s text into multiple columns in Google Sheets using built-in tools like SPLIT and TEXTSPLIT. You’ll learn when to use each function, how to handle common delimiters, and how to apply formulas across rows. Practical examples and best practices are included.
Understanding the need to split text in Google Sheets
Data often arrives in a single cell, packed with multiple values separated by commas, semicolons, pipes, or tabs. The ability to split that text into separate columns is a foundational skill for clean analysis, sorting, and reporting. When you perform a google sheets split text to columns, you unlock easier filtering, targeted calculations, and more reliable downstream processing. This guide emphasizes practical methods, common delimiters, and real-world examples to help students, professionals, and small business owners work more efficiently in Sheets. You’ll also learn how to handle spaces, quotes, and inconsistent data layouts so your results remain accurate across datasets of varying quality. Throughout, you’ll see how these techniques fit into broader data-management workflows and how they compare to similar tasks in other platforms.
Core tools: SPLIT and TEXTSPLIT
Google Sheets offers SPLIT as the go-to for turning a single string into multiple cells across columns. The formula is simple: =SPLIT(text, delimiter, [split_by_each], [remove_empty_text]). The function spills results into the adjacent columns to the right. TEXTSPLIT, on the other hand, adds flexibility by allowing two delimiters (one for rows, one for columns) or more nuanced control over empty values. This is especially useful when your data uses more than one boundary rule or when you want to shape the output grid precisely. The How To Sheets team emphasizes testing formulas on a sample before applying them to large sheets to avoid accidental data overwrites. As you practice, remember that correct delimiter selection is the key to clean splits and reliable results.
Basic example: split a single cell by a comma
Suppose cell A2 contains the value "Apple, Banana, Cherry" and you want three separate columns. Use the SPLIT function: =SPLIT(A2, ","). This simple approach demonstrates the core concept: a delimiter defines where the text breaks into new columns. If your data includes spaces after the comma, wrap SPLIT with TRIM: =TRIM(SPLIT(A2, ",")). For some datasets, it helps to clean the input first with SUBSTITUTE to remove unwanted characters. When you apply this to a range (A2:A), you can spill across multiple rows with careful destination planning to avoid overwriting headers or data.
Advanced example: splitting with multiple delimiters and ignoring blanks
When data uses multiple delimiters, SPLIT can handle it by including all delimiters in the set: =SPLIT(A2, ",;"). You can also control how blanks appear with the optional arguments [split_by_each] and [remove_empty_text], e.g., =SPLIT(A2, ",;", TRUE, TRUE). TEXTSPLIT supports more complex layouts with two delimiters (one for rows, one for columns) and better handling of empty values. In practice, clean data first (TRIM, CLEAN, and SUBSTITUTE) to reduce stray spaces and quotes that can clutter your results. Always validate a small sample before scaling up.
Working with ranges: applying to entire column using ARRAYFORMULA
To apply a split across many rows without copying formulas, you can wrap SPLIT in ARRAYFORMULA. A typical pattern is =ARRAYFORMULA(SPLIT(A2:A, ",")), which spills results across columns and rows. If you want to skip entirely blank rows, use: =ARRAYFORMULA(IF(A2:A="", , SPLIT(A2:A, ","))). Be mindful: SPLIT returns multiple columns, so ensure there is enough empty space to accommodate the spill. Test on a subset first to avoid disrupting adjacent data.
Common issues and troubleshooting
Leading or trailing spaces often distort splits. Use TRIM around the input or within the SPLIT result to normalize values. Quoted values or embedded delimiters can also cause unexpected results; consider removing or normalizing quotes first with SUBSTITUTE. If output columns are inconsistent, double-check the delimiter and consider using REGEXEXTRACT or TEXTSPLIT for more precise control. When working with large datasets, consider batch processing in smaller sheets to keep performance reasonable and to avoid hitting formula recalculation limits.
Quick-start templates and tips
Templates to get you started:
- Basic split: =SPLIT(A2, ",")
- Split with ignore_empty_text: =SPLIT(A2, ",", TRUE, TRUE)
- Range split with array formula: =ARRAYFORMULA(IF(A2:A="", "", SPLIT(A2:A, ",")))
Tips:
- Always test with a small sample first.
- Use TRIM to remove stray spaces.
- If you see quotes around results, clean the data before splitting.
- For complex layouts, explore TEXTSPLIT and two-delimiter configurations.
- Keep a backup of the original data before performing large spills.
Practical tips and real-world considerations
In practice, begin with a clear data-cleaning plan. If your data includes multiple text entries in one cell that must stay together (e.g., addresses with commas), SPLIT may not be the best choice without pre-processing. In those cases, TEXTSPLIT or a combination of REGEXREPLACE and SPLIT can preserve important groupings. As you gain experience, you’ll develop a mental map: simple comma-delimited data tends to be best served by SPLIT, while multi-dimensional grids benefit from TEXTSPLIT. Finally, save your formulas in a template so you can reuse them on future datasets with the same structure.
Tools & Materials
- Google Sheets access(Ensure you have edit rights to the target spreadsheet.)
- Sample dataset with mixed text(Include commas, semicolons, or pipes for practice.)
- Delimiter reference (comma, semicolon, pipe)(Optional: keep handy on a separate sheet.)
- Blank worksheet for results(Use a new tab to avoid overwriting data.)
Steps
Estimated time: 15-25 minutes
- 1
Identify the split target
Locate the cell or column you want to split and determine the output area where the results will spill. Check for neighboring data to avoid overlaps. Confirm whether you’ll process a single cell or a range.
Tip: Label the output area to prevent accidental data overlap. - 2
Choose the delimiter
Decide which character or pattern separates the values (comma, semicolon, pipe, or tab). If the delimiter appears in the data, consider a more robust method like TEXTSPLIT with two delimiters.
Tip: Make a quick sample to verify the delimiter splits as expected. - 3
Write the basic SPLIT formula
For a single-cell split, enter =SPLIT(A2, ","). If spaces exist after the delimiter, wrap with TRIM: =TRIM(SPLIT(A2, ",")).
Tip: Start in a spare area to validate results before moving to the final output region. - 4
Extend to a range with ARRAYFORMULA
To apply to many rows, use an array approach: =ARRAYFORMULA(IF(A2:A="", "", SPLIT(A2:A, ","))). This spills results across multiple rows and columns.
Tip: Test with a short range first to avoid spills into protected areas. - 5
Handle blanks and duplicates
If your data contains blanks, use the ignore_empty_text parameter: =SPLIT(A2, ",", TRUE, TRUE). For more complex layouts, add TRIM or CLEAN beforehand.
Tip: Combine with IFERROR to surface meaningful messages instead of errors. - 6
Clean and validate results
After splitting, inspect the columns for consistency. Remove any stray spaces, quotes, or missing segments. Compare the number of resulting columns across rows to ensure uniformity.
Tip: Use conditional formatting to spot anomalies quickly. - 7
Convert results to static data
If you need to freeze the results, copy the split output and paste values back into the sheet.
Tip: Paste values only to preserve the final data type and formatting. - 8
Document and reuse
Add a short note in the sheet about the split approach and save formulas as a template for future datasets.
Tip: Create a small template file that matches common data structures.
FAQ
What is the difference between SPLIT and TEXTSPLIT?
SPLIT divides a single string into columns. TEXTSPLIT can handle two dimensions with two delimiters, offering more flexible layouts for complex data.
SPLIT is great for simple splits, while TEXTSPLIT helps when you need to control both rows and columns with different delimiters.
Can I split by multiple delimiters at once?
Yes. TEXTSPLIT can handle two delimiters, and SPLIT can be combined with REGEX or multiple SPLIT calls for more complex cases.
Yes—use TEXTSPLIT for multi-delimiter layouts, or combine SPLIT with REGEX for more control.
How do I ignore blanks after splitting?
Use the remove_empty_text parameter in SPLIT, e.g., =SPLIT(text, delimiter, TRUE, TRUE), or clean the data first with TRIM.
Use the ignore_empty option, or trim the data to prevent empty cells from appearing.
What should I do with leading or trailing spaces?
Wrap the source with TRIM or apply TRIM after splitting to normalize values. For complex data, combine TRIM, CLEAN, and SUBSTITUTE.
Trim first to avoid extra spaces, which can create misaligned results.
Is there a way to split across an entire column automatically?
Yes, use ARRAYFORMULA with SPLIT, e.g., =ARRAYFORMULA(IF(A2:A="", "", SPLIT(A2:A, ","))). Always test on a small range first.
You can auto-split a whole column with ARRAYFORMULA, but verify the spill area is clear.
Are there performance concerns with large datasets?
Splitting very large datasets can slow down sheets. Consider batching your splits or using Apps Script for heavy workloads.
Large splits can slow things down; consider splitting data in smaller chunks or scripting for heavy use.
Watch Video
The Essentials
- Use SPLIT for simple splits and TEXTSPLIT for two-dimensional layouts
- Apply to ranges with ARRAYFORMULA to save time
- Clean data before splitting to improve results
- Verify outputs and avoid overwriting source data
