You’re using Split text to columns in Google Sheets, but it’s not splitting data correctly, or not splitting at all.
This usually shows up as data staying in one column, splitting into wrong columns, or inconsistent results across rows.
This guide fixes it step by step.
Why the Issue Happens
- Wrong delimiter selected (comma, space, tab, etc.)
- Inconsistent delimiters in the dataset
- Extra spaces or hidden characters
- Text not recognized as a delimiter (special symbols)
- Merged cells blocking split
- Data already formatted or partially split
- Using wrong encoding (imported data issues)
- Multiple delimiters mixed in one column
Step-by-Step Fixes
Step 1: Select the Correct Column
Text to columns only works on selected data.
Fix:
- Select the entire column or range:
A2:A100
Then go to:
- Data → Split text to columns
If you select partial data, results may be inconsistent.
Step 2: Choose the Correct Delimiter
After splitting, choose the correct separator.
Common options:
- Comma (,)
- Space
- Semicolon (;)
- Custom
If wrong delimiter is selected, data won’t split correctly.
Step 3: Use Custom Delimiter (Most Common Fix)
If your delimiter isn’t standard:
Example:
John|Sales|100
Fix:
- Select Custom
- Enter:
|
This forces correct splitting.
Step 4: Remove Extra Spaces
Data may contain irregular spacing.
Example:
John , Sales , 100
Fix before splitting:
=TRIM(A2)
Or clean entire column:
=ARRAYFORMULA(TRIM(A2:A100))
Then apply split again.
Step 5: Handle Multiple Delimiters
If data uses different separators:
Example:
John,Sales | 100
Text to columns won’t handle this well.
Fix:
=SUBSTITUTE(A2, "|", ",")
Then split by comma.
Step 6: Use SPLIT Function (More Reliable)
Instead of menu option:
=SPLIT(A2, ",")
For custom delimiter:
=SPLIT(A2, "|")
For multiple delimiters:
=SPLIT(SUBSTITUTE(A2,"|",","), ",")
This gives more control than UI tool.
Step 7: Remove Hidden Characters
Imported data may contain invisible characters.
Fix:
=CLEAN(A2)
Combine with TRIM:
=TRIM(CLEAN(A2))
Then split.
Step 8: Check for Merged Cells
Splitting won’t work on merged cells.
Fix:
- Select range
- Format → Merge cells → Unmerge
Step 9: Ensure Enough Empty Columns
Split needs space to expand.
Error:
- Data doesn’t split or overwrites existing values
Fix:
- Insert empty columns to the right
- Or clear adjacent cells
Step 10: Fix Encoding Issues (Imported Data)
If data comes from CSV or external source:
- Delimiters may not be recognized properly
Fix:
- Re-import with correct delimiter
- Or use SPLIT manually
Common Mistakes
- Choosing wrong delimiter
- Not cleaning spaces before splitting
- Ignoring mixed delimiters
- Using Text to Columns on merged cells
- Not leaving enough space for split
- Expecting tool to handle complex formats automatically
- Not using SPLIT for advanced cases
Pro Tips / Better Alternatives
Use SPLIT with ARRAYFORMULA
=ARRAYFORMULA(SPLIT(A2:A100, ","))
Splits entire column dynamically.
Combine SPLIT with TRIM
=ARRAYFORMULA(TRIM(SPLIT(A2:A100, ",")))
Cleans data while splitting.
Use REGEX for Complex Splits
=REGEXSPLIT(A2, "[,|;]")
Splits using multiple delimiters at once.
Use QUERY After Splitting
=QUERY(SPLIT(A2:A100, ","), "SELECT Col1, Col2")
Organize split data efficiently.
Pre-Clean Data Pipeline
Before splitting:
=TRIM(CLEAN(A2))
Clean data ensures accurate results.
Bottom Line
If text to columns isn’t working, fix in this order:
- Select correct column and range
- Choose the right delimiter
- Clean spaces and hidden characters
- Handle multiple delimiters
- Use SPLIT for better control
- Ensure enough empty space
Most issues come from delimiter mismatch and dirty data.
Fix those, and splitting will work accurately.
Google Sheets Fixes: