Nearly found the answer here: First non blank cell in row as an array for the column But this formula only returns the first word of each cell.
Here's my example sheet
COLUMN M is the copied formula from the above stackoverflow answer.
The only way I can get the result I need is by duplicating the formula using '&', adding a space (" ") and TRIM() at the beginning as it adds additional spaces.
Is there any way of simplifying my formula in COLUMN N?
I added a new sheet ("Erik Help") with the following formula in M1:
=ArrayFormula({"Header of Choice";IF(A2:A="",,TRIM(TRANSPOSE(QUERY(TRANSPOSE(B2:L)," ",COLUMNS(B2:L)))))})
As I see it, this is about as concise, flexible and powerful as the formula can get. It takes advantage of a quirk in how QUERY headers are handled.
This formula can handle any number of words in a cell. Of note, it assumes (as shown in your sheet) that there will only be one string to return per row in B:L. If there will be more than one possible return per row, the formula can be easily modified to include a delineator.
shorter:
={"SHORT FX"; INDEX(IF(A1:A="",,TRIM(
IFERROR(INDEX(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(B2:L),,9^9))), " "),,1))&" "&
IFERROR(INDEX(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(B2:L),,9^9))), " "),,2))&" "&
IFERROR(INDEX(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(B2:L),,9^9))), " "),,3))
)))}
={"SHORTEST"; INDEX(IF(A2:A="",,TRIM(FLATTEN(QUERY(TRANSPOSE(B2:L),,9^9)))))}
