range to last cell with value

[Urraco]

Hi,

=TEXTJOIN(" ",TRUE,A2:B51)

How could I replace "B51" with "LOOKUP(2,1/(B:B<>""),B:B)" which is the last cell value from column B?

Thanks

 

[Fluff]

Why not just use something like A2:B100 as the formula is ignoring blank cells.

 

[Joe4]

That LOOKUP formula will not work, as it returns the value from the last row, not the last row number.

Try this:

=TEXTJOIN(" ",TRUE,INDIRECT("A2:B"&SUMPRODUCT(MAX((B:B<>"")*ROW(B:B)))))

 

[Urraco]

Why not just use something like A2:B100 as the formula is ignoring blank cells.

thanks for reply
because I need only value..
blank cells contain "carriage return"

 

[Urraco]

That LOOKUP formula will not work, as it returns the value from the last row, not the last row number.

Try this:

=TEXTJOIN(" ",TRUE,INDIRECT("A2:B"&SUMPRODUCT(MAX((B:B<>"")*ROW(B:B)))))

thanks for solution!

 

[Joe4]

You are welcome.
Glad we could help!