Forum Discussion
Solved
Excel formula - returning multiple text strings in a cell
Hi, Newbie here. Is have a cell that could contain apples and/or oranges and/or pears. I'm able to use an IF formula to extract either apples or oranges or pears but get a false value when the cell c...
As variant if add list of possible variant in column O, and assuming your version of Excel supports TEXTJOIN,
in M2 is
=IF( LEN(TEXTJOIN(", ",1,IF(COUNTIF(B2,"*"&$O$2:$O$4&"*"),$O$2:$O$4,"")))=0, "apple", TEXTJOIN(", ",1,IF(COUNTIF(B2,"*"&$O$2:$O$4&"*"),$O$2:$O$4,"")) )and drag it down.
hynguyenThank you for taking the time to build this example. It looks very complicated. I'll try to see if I can use it in the example I've just attached. Much appreciated.
As variant if add list of possible variant in column O, and assuming your version of Excel supports TEXTJOIN,
in M2 is
=IF(
LEN(TEXTJOIN(", ",1,IF(COUNTIF(B2,"*"&$O$2:$O$4&"*"),$O$2:$O$4,"")))=0,
"apple",
TEXTJOIN(", ",1,IF(COUNTIF(B2,"*"&$O$2:$O$4&"*"),$O$2:$O$4,""))
)and drag it down.
