CODEWITHSUNDEEP
google-sheetsarray-formulas
SaroGave
SaroGave

Reputation: 1

Using Array Formula, return any non blank cell in row

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?

Upvotes: 0

Views: 1523

Answers (2)

player0
player0

Reputation: 1

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))
)))}

enter image description here

UPDATE:

={"SHORTEST"; INDEX(IF(A2:A="",,TRIM(FLATTEN(QUERY(TRANSPOSE(B2:L),,9^9)))))}

enter image description here

Upvotes: 0

Erik Tyler
Erik Tyler

Reputation: 9345

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.

Upvotes: 1

Related Questions