split() function skips blanks

google-sheets

Solution

I don't think you can specify that directly, though here is a workaround:

- Add another delimiter character to your string. So replace `,` with say `,|`

- Now when you split with `,`, we know for sure that even empty columns will have a character (in this case `|`)

- Use replace to substitute the extra delimiter `|` with a blank string

- Since output of `split` is an array, you will need to use `arrayformula`

Here is what the final formula would look like

=arrayformula(substitute(split(substitute(A1,",",",|"),","), "|",""))

Problem

In `A1` cell I have `A,B,C,,E`. I want to split the cell into five cells: `A` , `B`, `C`, ``, `E` This formula `=split(A1,",")` splits into four cells `A`, `B`, `C`, `E` and skips over the blank. How do I tell it to split "properly"?

Original source