Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Split error
Excel & Google SheetsWhat Split means
Text to Columns (Excel) and SPLIT (Google Sheets) split cell content on a specified delimiter. When nothing splits, the detected delimiter does not match the actual separator in the data — often because the apparent delimiter is a non-breaking space, a different invisible character, or the data is already a number that cannot be split as text.
Common causes
- The delimiter chosen (e.g., comma) does not match what is actually in the data — the separator may be a tab, semicolon, or pipe character instead.
- The apparent spaces are non-breaking spaces (char 160) or other Unicode space characters, not standard spaces (char 32) that Text to Columns splits on.
- The source cells contain numbers or dates already stored as their native type, not as text, so there is nothing to split.
- In Google Sheets, SPLIT treats consecutive delimiters as one by default — if you need to split on each delimiter (even empty segments), set split_by_each or each_occurrence appropriately.
- The data was imported and contains invisible line-feed or carriage-return characters (char 10 / char 13) that need CLEAN() before splitting is possible.
Example fix
Broken
=SPLIT(A2,",")Fixed
=SPLIT(SUBSTITUTE(A2,CHAR(160),","),",")The data used non-breaking spaces (CHAR(160)) as separators rather than regular commas. SUBSTITUTE converts them to commas first so SPLIT can find them.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1In Excel's Text to Columns wizard, on step 2, select 'Other' and manually type or paste the exact delimiter character from the cell using Find & Replace first to identify it.
- 2Use =CODE(MID(A2,2,1)) on a character in the data to find its ASCII/Unicode code point and identify the actual separator.
- 3Replace non-breaking spaces before splitting: use Find & Replace (Ctrl+H), put your cursor in Find, press Ctrl+J or Alt+0160, and replace with a regular space.
- 4Apply CLEAN(TRIM(A2)) to a helper column to remove hidden line-feed/carriage-return characters, then run Text to Columns on that column.
- 5In Google Sheets, for complex multi-character delimiters, use REGEXEXTRACT instead of SPLIT: =REGEXEXTRACT(A2,"(.+?)\s+-\s+(.+)") to capture groups.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Split and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
Text to Columns worked once but the next import has the same problem again. How do I automate the fix?
Use a formula-based split in a helper column: =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),(N-1)*100+1,100)) for the Nth word, or use Power Query (Get & Transform) which remembers the split step and replays it on refresh.
How do I split on multiple different delimiters at once?
In Excel, Text to Columns only supports one delimiter at a time. Use SUBSTITUTE to normalize all delimiters to a single character first, then split. In Sheets, SPLIT(A2,",;|") splits on any of those three characters.
After splitting, the values show as text even though they should be numbers. How do I fix that?
Wrap each SPLIT result in VALUE(): =VALUE(TRIM(SPLIT(A2,","))) or select the split output cells and use Data > Text to Columns with no delimiter change to trigger Excel's automatic type conversion.