Excel formula to get non-blanks from a range - row by row

[Js Smith]

Hi all!

I'm not able to find a suitable formula in google searches.

This is my fake data which could start in column B, X or anywhere else:

stuff1	stuff2	stuff3	stuff4	stuff5	stuff6	stuff7	stuff8
Item1b	Item2c	Item2d		Item2f	Item2g		Item3i
Item2b
Item1b	Item2c	Item2d		Item2f	Item2g		Item3i
			item3e
Item1b	Item2c	Item2d		Item2f	Item2g		Item3i
Item1b	Item2c	Item2d			Item2g

What I need is an excel formula to get non-blanks from a range - row by row so it looks something like this:

Item1b	Item2c	Item2d	Item2f	Item2g	Item3i
Item2b
Item1b	Item2c	Item2d	Item2f	Item2g	Item3i
item3e
Item1b	Item2c	Item2d	Item2f	Item2g	Item3i
Item1b	Item2c	Item2d	Item2g

Just for fun, the data could have a blank row so not sure how to work that out for error handling.

I've played around with this sort of formula

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISTEXT($A$2:$A$10),ROW($A$1:$A$9),""),ROW(A1))),"")

but can't get it to do what I need.

There's loads of articles out there dealing with columns and with fairly clean data but not messy rows.

Thanks, in advance, for your guidance!

 

[Js Smith]

Also tried something like this but couldn't get it to work with a range, rather than the entire row

=IFERROR(INDEX(2:2,AGGREGATE(15,6,(1/(2:2<>""))*COLUMN(2:2),COLUMN(A1))),"")