Notifications
Q&A

Copy a sparse matrix with removal of blanks

+0
−0

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

Array example

https://docs.google.com/spreadsheets/d/1nPTBFXaIgpruPgNoWFSBUL2yKaibjeNJNExhnHVr8LE/edit#gid=0

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?

0 comments

1 answer

+0
−0

No. In B2 and copied down to suit:

=split(textjoin(",",1,D2:I2),",")

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.

0 comments

Sign up to answer this question »