Find the one cell in a row range with (any) text string and output that word into the result cell

Question

I am looking for a formula in Excel to search a row of say 5 cells, find the one with a string in it and output that text in the result cell.

There are loads of articles for checking a range for a specific word and saying whether that range does or doesn't contain it but I am not finding one for searching for any text in a row range, where if it's true that one of the cells is found to contain a text value, to output that text in the result column.

My rows will only ever have one word in one of the 5 cells the other 4 will always have FALSE. I assume ISTEXT might be the way forward?

Example data: Data in that row is eg FALSE, FALSE, Apples FALSE, FALSE, where apples might be any other single word and it might be in any of the 5 columns. And FALSE is just a prior formula output so probably not actually a string. Desired result is to have Apples in F2. The next row might have oranges in E3 and FALSE in the other cells so wish to have the word oranges output in F3

I would have done it manually but have 29,000 rows, Thank you.

Answer 1

FALSE (as a boolean) concatenated to a string value just becomes FALSE (as a string) - so you can use SUBSTITUTE to remove all instances of FALSE by replacing it with "". As your rules state: My rows will only ever have one word in one of the 5 cells the other 4 will always have FALSE - this will leave just the string value:

=SUBSTITUTE(A2&B2&C2&D2&E2,"FALSE","")