Arrayformula to return last non-empty cell in multiple rows

Question

I need help formulating an arrayformula on how to return the last non-empty cell in a row across multiple rows.

The formula will be in column A in cell A2 and the output will look like this in column A:

The formula I have is only for 1 row. I need the formula (only 1 formula) to return the last non-empty cells of 1000+ rows.

This is the current formula I have but it's only for 1 row. I need to drag it down every time I add a new row:

=LOOKUP(1, ARRAYFORMULA(1/(B2:Z2<>"")),B2:Z2)

Is there an arrayformula that can do this across multiple rows without dragging down the formula?

any help would be appreciated. Thank you very much.

Answer 1

try:

=INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 SUBSTITUTE(B2:Z, " ", CHAR(13))),,9^9))), ".* ", ))