Cleaning imported data
TEXTSPLIT function: split semicolon or comma strings into columns
Break a delimited import string across columns dynamically with TEXTSPLIT, and control whether consecutive delimiters keep blank slots or collapse.
Semicolon-separated fields pasted into a single column
European CSV exports frequently paste into column A as "Ada;Lin;Berlin;92" (A2), or with a missing middle city as "Omar;;Oslo;88" (A3).
| Raw line | 3rd field (keep empty) | Token count (ignore_empty=TRUE) |
|---|---|---|
| Ada;Lin;Berlin;92 (A2) | Berlin | 4 |
| Omar;;Oslo;88 (A3) | Oslo | 3 |
Keep column alignment when fields can be blank
By default ignore_empty (the fourth argument) is FALSE, which preserves column positions: in "Omar;;Oslo;88", field 2 is empty and field 3 is still "Oslo". If you set ignore_empty to TRUE, empty slots are skipped and "Oslo" shifts left into position 2—useful for tag lists, dangerous for fixed-column records!
=INDEX(TEXTSPLIT(A2,";"),1,3)Count non-empty tags with ignore_empty = TRUE
When splitting a free-form tag list where accidental double semicolons should be ignored, pass TRUE as the fourth argument: =COUNTA(TEXTSPLIT(A3, ";", , TRUE)) counts 3 non-empty tokens.
=COUNTA(TEXTSPLIT(A3,";",,TRUE))Try the example
Use the original values or change them to test your understanding.