"Split" delimited string, preserving blank entries

Question

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

Answer 1

Try the following solution.

Formula

=ARRAYFORMULA(TRIM(SPLIT(JOIN(" ,", A1:A4,C1:C4), ",")))

Explained

You already showed us that the SPLIT function ignores empty cells, therefore I choose this string as delimiter: " ,". The intermediate result of the JOIN function, looks like this:

I choose the delimiter for the SPLIT function to be this: ",". The intermediate result of the SPLIT function, looks like this:

Now, however, we have to deal with the extra added white spaces. Therefore I used the TRIM function, to remove all trailing or leading white spaces (in combination with the ARRAYFORMULA). Compare the LEN values of each intermediate result.

Result

Example

I've created an example file for you: SPLIT to preserve blank cells

Answer 2

Try the following solution.

Code

function myJoin() { 
  var output = []; 
  for(var i = 0, iLen = arguments.length; i < iLen; i++) {
    for(var r = 0, rLen = arguments[i].length; r < rLen; r++) {
      output.push(arguments[i][r][0]);
    }    
  }
  return [output];
}

Usage

Add as many ranges to the function as you like and they will be processed. Add this little script into the script editor of your spreadsheet (Tools>Script editor; Press save button).

Example

I've created an example file for you: SPLIT to preserve blank cells

Answer 3

Short answer

The following formula returns the desired result.

=Transpose({A1:B4;C1:D4})

Extended answer

This answer is focused in providing a way to get the desired result instead of discussing how to make the chosen approach to work.

The XY Problem

The OP want to get X and thinks that Y should return that result. This approach is problematic because Y has some caveats. This could be a mayor problem as the OP ask about how to make Y to work instead of asking for a way to get X but it's not so big because it's provided context and the desired result.

Solution

Google Sheets is able to handle multi-value results in a very simple way from the point of view of end users. By the other hand, it allow to to use a special notation to allow end users to define arrays.

The above features make possible to obtain the expected result without the use of the SPLIT() and JOIN() functions and without the caveats inherent to them.

Formula explanation

{A1:B4;C1:D4}

It's an 2 X 8 array made by two 2 X 4 arrays/ranges. The semicolon (;)is used assuming that the decimal separator of the spreadsheet is a dot (.). If the decimal separator were a comma, replace it by a backslash (\).

=Transpose({A1:B4;C1:D4})

Converts the 2 X 8 array into a 8 X 2 array.

One of the advantages of this method is that it doesn't require special handling of blank cells. Other advantages are that it's a very compact formula and it's very easy to adapt.

References

• What is the XY Problem
• Using arrays in Google Sheets - Google Docs editors Help

Answer 4

If you join and make the delimiter to be " ," basically a comma with a preceding space it will automatically preserve that space when you split it again:

=SPLIT(JOIN(" ,",A1:A4),",")