return the value of the first cell in a row to contain text

[jondavis1987]

Cells A14:BA14 all contain formulas. Only one of them will produce anything at a time. They all produce different text. I am trying for a formula to produce the cell value of the one that has text. For instance, if AW14 says "Silty clay", all of the other cells in the row will be blank. I'd like my formula to produce the value of AW14, saying "Silty clay" itself.

 

[oldbrewer]

silty clay

4

let A1 to E1 be the cells and only one will have text, the others will

have a blank returned by a formula

so A1 B1 C1 E1 are all blank

next time B1 might equal red

to get the value each time use Mr Concatenate

silty clay

=A1&B1&C1&D1&E1

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[njimack]

Confirm with ctrl+shift+enter:
=INDEX(A14:BA14,,MATCH(1,--(LEN(A14:BA14)>0),0))

 

[jondavis1987]

Thank you! Both work.