Forum Discussion

moncho47's avatar
moncho47
Copper Contributor
Jun 06, 2020
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...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 06, 2020

    moncho47 

    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.

moncho47's avatar
moncho47
Copper Contributor
Jun 06, 2020

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.

hynguyen's avatar
hynguyen
Iron Contributor
Jun 06, 2020

moncho47I tried to show the thought process in producing the desired output in separate columns so that you can understand the ultimate formula. You only have to copy the formula in column H (as below) to where you want to display your result and change $D$1 to "apple", $E$1 to "orange", $F$1 to "pear" if you prefer hardcoding them. Note that the cells containing your text is assumed to be in column A, otherwise change its reference in the formula as well. I think this formula is not at all complicated 🙂

=TEXTJOIN("/",TRUE,IF(ISNUMBER(SEARCH($D$1,A2)),$D$1,""),IF(ISNUMBER(SEARCH($E$1,A2)),$E$1,""),IF(ISNUMBER(SEARCH($F$1,A2)),$F$1,""))

  • moncho47's avatar
    moncho47
    Copper Contributor
    Jun 07, 2020
    Thank you hynguyen.

Resources