Discussion Re: Excel formula - returning multiple text strings in a cell in Excel
https://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445583#M64873
<P><LI-USER uid="691078"></LI-USER> </P>
<P>As variant if add list of possible variant in column O, and assuming your version of Excel supports TEXTJOIN,</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 291px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/197033iD4D8ECE3CB0C823F/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>in M2 is</P>
<LI-CODE lang="excel">=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,""))
)</LI-CODE>
<P>and drag it down.</P>Sat, 06 Jun 2020 19:07:47 GMTSergei Baklan2020-06-06T19:07:47ZExcel formula - returning multiple text strings in a cell
https://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445489#M64853
<P>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 contains 'apples and oranges'. Is there a way to extract such a combination out of a cell?</P>Sat, 06 Jun 2020 17:04:00 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445489#M64853moncho472020-06-06T17:04:00ZRe: Excel formula - returning multiple text strings in a cell
https://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445530#M64858
<P><LI-USER uid="691078"></LI-USER> </P><P> </P><P>Could you post a representative spreadsheet that illustrates what you're describing. One that works on the one hand, but not on the other. It's hard to diagnose without actually seeing and experiencing the reality, no matter how clear your description is (and it's pretty good)...</P><P> </P><P>I do realize that your apples and oranges is meant as an example, but I'm having a hard time visualizing why you can make the one condition work, but not the other.</P>Sat, 06 Jun 2020 17:51:28 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445530#M64858mathetes2020-06-06T17:51:28ZRe: Excel formula - returning multiple text strings in a cell
https://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445536#M64859
<P><LI-USER uid="691078"></LI-USER> 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.</P>Sat, 06 Jun 2020 17:56:16 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445536#M64859hynguyen2020-06-06T17:56:16ZRe: Excel formula - returning multiple text strings in a cell
https://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445568#M64867
<P><LI-USER uid="425987"></LI-USER>Thank you for replying. Much appreciated. I've attached an example of what I'm trying to get at. Hope it makes sense. I'm amazed at what Excel can do but not sure if what I'm hoping to do is beyond it (I doubt it!)</P>Sat, 06 Jun 2020 18:43:47 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445568#M64867moncho472020-06-06T18:43:47ZRe: Excel formula - returning multiple text strings in a cell
https://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445573#M64869
<P><LI-USER uid="675276"></LI-USER>Thank 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.</P>Sat, 06 Jun 2020 18:51:15 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445573#M64869moncho472020-06-06T18:51:15ZRe: Excel formula - returning multiple text strings in a cell
https://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445583#M64873
<P><LI-USER uid="691078"></LI-USER> </P>
<P>As variant if add list of possible variant in column O, and assuming your version of Excel supports TEXTJOIN,</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 291px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/197033iD4D8ECE3CB0C823F/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>in M2 is</P>
<LI-CODE lang="excel">=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,""))
)</LI-CODE>
<P>and drag it down.</P>Sat, 06 Jun 2020 19:07:47 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445583#M64873Sergei Baklan2020-06-06T19:07:47ZRe: Excel formula - returning multiple text strings in a cell
https://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445606#M64875
<P><LI-USER uid="691078"></LI-USER>I 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 <LI-EMOJI id="lia_slightly-smiling-face" title=":slightly_smiling_face:"></LI-EMOJI></P><P>=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,""))</P>Sat, 06 Jun 2020 19:42:25 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1445606#M64875hynguyen2020-06-06T19:42:25ZRe: Excel formula - returning multiple text strings in a cell
https://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1446939#M64935
Thank you hynguyen.<BR />Sun, 07 Jun 2020 17:06:50 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-returning-multiple-text-strings-in-a-cell/m-p/1446939#M64935moncho472020-06-07T17:06:50Z