Forum Discussion
Excel formula - returning multiple text strings in a cell
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 If you do not care for the exact order of occurrence of the found texts in the cell, the attached workbook may return the results you want (use the formula in the last column, assuming that you have a list of your search texts in cell D1, E1, F1). It is always better to have the search texts listed and use reference in the formula to those cells rather than hardcoded. You can always expand the list of your search texts and simply add more components to the formula.
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.
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,""))
- Thank you hynguyen.
