Copy a sparse matrix with removal of blanks


A user of Web Applications gave the following as an example:

Array example

and asked:

How do I read a long row of cells only some of which have scores in them, and print those scores out?

The user tried formulae but ended up writing a script.

Is a script necessary?

Why should this post be closed?


1 answer


No. In B2 and copied down to suit:


TEXTJOIN strings all the cells together (in this case separated by ,s and with the option to exclude blank cells). Then SPLIT with "," as marking the point to divide, breaks the result apart.

If any cells already contain , a different separator may be chosen.


Sign up to answer this question »