0

I want to remove specific (or any) values in dynamic array/range and return the result of the resized array without those values without using filter() as I need to use arrayformula.

For example,

{"","","A","","B"} // size: 5

to

{"A","B"} // size: 2

Is there a way to do this?

2
0

I found a way to do this using a custom function. The array arrives in the function as an object containing (in general) a 2D array. This can be edited to delete the null strings, but that still leaves 'null' attributes in the object. JSON stringification allows final removal.

/*
 * Removes null elements from a two dimensional range.
 * The array is passed by Google sheets as an object containing a two dimensional array.
 */


function condense(as)
{
   for (r in as) { for (e in as[r] ) { if (as[r][e]===""){delete as[r][e];}}
                   if (as[r].every(function(e,i,a) {e===null;})){delete as[r];}
                 }
 return JSON.parse(JSON.stringify(as).replace(/null,/g,"").replace(/,null]/g,"]"));
 }

Then:

=ARRAYFORMULA(condense({"","","A","","B"}))

Produces A and B in adjacent cells.

The function also works for general 2D ranges. For example

[["","",""],["","zero",""],["one","two",""],["three","four",""],["","",""]]

reduces to

[["zero"],["one","two"],["three","four"]]

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.