dimanche 23 août 2020

How to copy cells to new location while removing blanks?

I am looking for a formula that reads one row of 5 cells, and fills the new row with the contents of the original row, but with the non-numeric cells removed and the rest shifted to the left:

Original

     *P*     *Q*     *R*     *S*     *T*
*23*          8       3               2

(Note: The "blank" cells aren't actually blank, the only way I can get them to read correctly in a formula is if I reference them as "". I am guessing this is because P23:T23 are also references to other cells, instead of direct numbers.)

Result

     *B*     *C*     *D*     *E*     *F*
*3*   8       3       2

I am looking for the formula to be executed in cell B3. I thought this would work:

=FILTER(P23:T23,NOT(ISBLANK(P23:T23)))

but, it didn't get rid of the blank cells, probably because Google Sheets doesn't think P23 & S23 are blank. Any ideas?

Edit: Here's a link to the sheet: https://docs.google.com/spreadsheets/d/14M6XggpIUXNdZ5ihQ7ipim44wsn4u-ywMZCG4kYMlQY/edit?usp=sharing

Aucun commentaire:

Enregistrer un commentaire