10

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
  • Would a space in those empty fields fix the issue? – ale Feb 28 '14 at 13:37
  • Typing " " does result in the blank entry being preserved (thanks, @AlE.!), but it would still be preferable to have a technological solution, not least because manually typing " " into all those entries would be both very time consuming and prone to error. – LiberalArtist Feb 28 '14 at 17:10
  • Spreadsheet formulas are not my forte. Perhaps something can be done with IF() to auto-convert empty fields to spaces. – ale Feb 28 '14 at 17:12
  • 1
    Update: @AlE.'s idea did let me get the computation done in the immediate term by using =ARRAYFORMULA(IF(ISBLANK(B1:B4)," ",B1:B4)) to make a dummy set of columns with " " in the blank cells, which then are used for SPLIT and JOIN. 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!) – LiberalArtist Feb 28 '14 at 23:00
3

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:
enter image description here

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

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

enter image description here

Example

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

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

enter image description here

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

2

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

1

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),",")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.