In my Google Sheet, the range B2:2 contains mostly empty cells, but some cells contain strings. I would like to print these strings into a transposed list beginning at A12. This list should exclude all empty cells and should print (in consecutive cells) all the strings in B2:2.

So far, I've tried =ArrayFormula(INDEX($B$2:$E$2, 0, SMALL(IF(NOT(ISEMPTY($B$2:$E$2)), COLUMN($B$2:$E$2)), ROW(1:1)) as various websites and Stack Exchange answers have suggested, and =ArrayFormula(INDEX($B$2:$E$2, 0, SMALL(IF($B$2:$E$2<>"", COLUMN($B$2:$E$2)), ROW(1:1)). However, these formulas have only simply transposed the entire range beginning at A12 when the B2 is filled. When B2 is not filled, no values appear.

How should I proceed?


If I understood correctly, a combination of filter (which filters out the empty cells, keeping only the non-blank cells) and transpose, should work:

=transpose(filter(B2:2, len(B2:2)))
| improve this answer | |

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.