How to filter blanks in a table using ARRAYFORMULA

Question

I want to copy down filter formula using Arrayformula. I also gave a hand to copy down add-on but it's not giving required result. Here is a picture of an example sheet:

I am filtering the non blank row-wise data. As data is coming from Google Form thus want to auto replicate this formula. Copy-down add-on and ARRAYFORMULA both are giving inaccurate results.

Answer 1

Please use the following formula

=INDEX(IFERROR(SPLIT( 
                  TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(B2:F),B2:F&"@",)&REPT(" "&"♣︎",COLUMN(B2:F)=COLUMN(F2))),"♣︎"))), 
                        "@")))

If on the other hand if you want to get just the first non-blank cells in each row, please use the following

=INDEX(IFERROR(SPLIT( 
                  TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(B2:F),B2:F&"@",)&REPT(" "&"♣︎",COLUMN(B2:F)=COLUMN(F2))),"♣︎"))), 
                       "@")),,1)

(I know they are a bit long... But they work)

Functions used:

• ArrayFormula
• IFERROR
• SPLIT
• TRANSPOSE
• TRIM
• CONCATENATE
• IF
• LEN
• REPT
• COLUMN