Google Sheets SPLIT Function Errors & How To Fix Them

Your SPLIT formula isn’t working, data isn’t splitting, splits are incorrect, or you see errors like #VALUE!.
This usually happens due to wrong delimiters, messy data, or incorrect formula structure.

Why the Issue Happens

• Wrong delimiter (comma, space, pipe, etc.)
• Inconsistent delimiters in the data
• Extra spaces or hidden characters
• Delimiter not actually present in the text
• Multiple delimiters not handled properly
• Not enough empty columns for expansion
• Text encoding issues (imported data)

Step-by-Step Fixes

Step 1: Use Correct SPLIT Syntax

Basic formula:

=SPLIT(A2, ",")

• A2 → text to split
• “,” → delimiter

Step 2: Verify the Delimiter

Check what actually separates the data.

Example:

John|Sales|100

Correct formula:

=SPLIT(A2, "|")

If delimiter is wrong → no split.

Step 3: Handle Spaces Properly

Problem:

John, Sales, 100

Fix:

=SPLIT(A2, ", ")

Or clean first:

=SPLIT(TRIM(A2), ",")

Step 4: Remove Hidden Characters

Imported data may contain invisible characters.

Fix:

=SPLIT(TRIM(CLEAN(A2)), ",")

Step 5: Handle Multiple Delimiters

If data contains different separators:

John,Sales|100

Fix:

=SPLIT(REGEXREPLACE(A2, "[|]", ","), ",")

Step 6: Ensure Enough Empty Columns

SPLIT expands horizontally.

If blocked → error or no result.

Fix:

• Clear cells to the right
• Ensure space for output

Step 7: Prevent Over-Splitting

By default, SPLIT splits at every delimiter.

Limit behavior:

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

• FALSE → treat delimiter exactly
• TRUE → ignore consecutive delimiters

Step 8: Use ARRAYFORMULA for Column Split

For entire column:

=ARRAYFORMULA(SPLIT(A2:A100, ","))

Step 9: Fix #VALUE! Error

Occurs when:

• Delimiter not found
• Formula applied incorrectly

Fix:

=IFERROR(SPLIT(A2, ","), "")

Step 10: Use REGEXSPLIT for Complex Cases

Better for multiple delimiters:

=REGEXSPLIT(A2, "[,|;]")

Splits on comma, pipe, or semicolon.

Common Mistakes

• Using wrong delimiter
• Ignoring spaces around delimiter
• Not cleaning imported data
• Not leaving space for expansion
• Expecting SPLIT to handle complex patterns automatically
• Mixing multiple delimiters without REGEX

Pro Tips

Combine with TRIM

=ARRAYFORMULA(TRIM(SPLIT(A2:A100, ",")))

Clean before splitting

=TRIM(CLEAN(A2))

Split and filter

=FILTER(SPLIT(A2, ","), SPLIT(A2, ",")<>"")

Bottom Line

If SPLIT isn’t working, fix in this order:

1. Use correct delimiter
2. Clean spaces and hidden characters
3. Ensure enough output space
4. Handle multiple delimiters properly
5. Use REGEXSPLIT for complex data

Most issues come from wrong delimiter or dirty data.
Fix those, and SPLIT will work correctly.