FILTER or QUERY multiple ranges with blanks removed

Question

I am trying to query or filter multiple ranges but am running into a situation with blank cells as I need all the ranges merged without blanks inbetween. Here is the sheet which has both my query and filter attempts.

Sample source data:

    A       B    C     D    E 
1
2   RANGE 1 JIM  JON  STAN  BOB
3   RANGE 2 JOE  LUKE MEL   
4   RANGE 3      DAVE DON   

Answer 1

QUERY has a inbuilt option to remove blanks:

=TRANSPOSE(QUERY(TRANSPOSE({B2:E2,B3:E3,B4:E4}),"select * where Col1 is not null"))

This is a alternative solution, which does not require you to list all the ranges (useful, if there are 1000s of ranges)

=ARRAYFORMULA(SPLIT(TRIM(CONCATENATE(QUERY(B2:E4&" ",, 50000)))," ", 0))

Answer 2

Please try:

=split(textjoin("|",1,QUERY({B2:E2,B3:E3,B4:E4},"select *")),"|")

TEXTJOIN has the option to exclude blank cells when concatenating them and the string can then be separated with SPLIT.