I am trying to use the SPLIT
and JOIN
functions in Google Sheets (the new version) to manipulate some data, and I am running into problems because SPLIT
is not preserving blank entries.
The data source is set up like this, essentially:
| A B C D
-----------------------
1 | q 5 r 2
2 | s t 4
3 | u 8 v
4 | w 3 x 6
(The blank entries in this data set represent "no measurement", which is distinct from a measurement of 0
)
On columns A
and C
, I'm getting the result I want by using the formula:
=SPLIT(JOIN("~",A1:A4,C1:C4),"~")
That produces a row that goes, as expected, q
s
u
w
r
t
v
x
, each in its own cell.
However, this method doesn't work on columns B
and D
. JOIN
functions as expected, giving the output:
5~~8~3~2~4~~6
Performing SPLIT
on that output, however, results in a problem: I'm left with 5
8
3
2
4
6
, without empty cells between 5
and 8
or between 4
and 6
, which means the pairs are broken up (e.g. s
and v
should correspond to empty cells, but instead w
and x
do). The problem seems to be that SPLIT
is interpreting ~~
as a single delimiter, rather than as two delimiters with a null entry between them.
Does anyone know how to preserve blank entries in this sort of scenario?
The desired output would look like this
q s u w r t v x
5 8 3 2 4 6
IF()
to auto-convert empty fields to spaces.=ARRAYFORMULA(IF(ISBLANK(B1:B4)," ",B1:B4))
to make a dummy set of columns with " " in the blank cells, which then are used forSPLIT
andJOIN
. Still hoping someone has a more elegant solution, though: this adds a bunch of extra stuff to my document, and it still needs me to update the row numbers in a few different places as more measurements are added to the data set. (Thanks to AI E. for at least letting me crunch the data for now!)