Discussion HOW TO: "If cell contains specific text then return specific text" in Excel
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69655#M8641
<P>I'm trying to extract a bunch of specific text combinations from cells and present it in a new cell.</P><P>This formula seems to work for two variables but I can't add any more variables too it.</P><P>=IFERROR(IF(SEARCH("*Sales*",B3,1),"Sales"),IF(SEARCH("*Arch*",B3,1),"Architecture"))</P><P> </P><P>The text I would be searching for would be:</P><P>Sales,</P><P>Arch,</P><P>Land,</P><P>ALL,</P><P>Contracts,</P><P>Construction</P><P>and possibly a couple more.</P><P> </P><P>Is there a way to do this?</P>Wed, 25 Jul 2018 16:36:29 GMTReuben2018-07-25T16:36:29ZHOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69655#M8641
<P>I'm trying to extract a bunch of specific text combinations from cells and present it in a new cell.</P><P>This formula seems to work for two variables but I can't add any more variables too it.</P><P>=IFERROR(IF(SEARCH("*Sales*",B3,1),"Sales"),IF(SEARCH("*Arch*",B3,1),"Architecture"))</P><P> </P><P>The text I would be searching for would be:</P><P>Sales,</P><P>Arch,</P><P>Land,</P><P>ALL,</P><P>Contracts,</P><P>Construction</P><P>and possibly a couple more.</P><P> </P><P>Is there a way to do this?</P>Wed, 25 Jul 2018 16:36:29 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69655#M8641Reuben2018-07-25T16:36:29ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69683#M8642
<P>Ah, a classic one.</P><P> </P><P>Key words in column F, returned text in column G, text to check in A1.</P><P> </P><PRE>=LOOKUP(PI(),1/COUNTIF(A1,"*"&$F$1:$F$6&"*"),$G$1:$G$6)</PRE><P> </P>Sat, 13 May 2017 05:12:59 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69683#M8642Detlef Lewin2017-05-13T05:12:59ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69741#M8643
<P>Dear Reuben Helder,<BR /><BR />You may also use the below formula for the task. I have retained the same cell references that have been used by Detlef Lewin in the solution that he has provided.<BR /><STRONG>=INDEX($F$1:$F$6,SUM(COUNTIF($A1,"*"&$F$1:$F$6&"*")*ROW(F1:F6)))</STRONG><BR /><BR />The formula given by Detlef Lewin can be amended, so that Pi() and column G will not be required.<BR /><STRONG>=LOOKUP(1,1/COUNTIF($A1,"*"&$F$1:$F$6&"*"),$F$1:$F$6)</STRONG><BR /><BR />Vijaykumar Shetye<BR />Spreadsheet Excellence,<BR />Panaji, Goa, India</P>Sat, 13 May 2017 19:04:50 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69741#M8643vijaykumar shetye2017-05-13T19:04:50ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69744#M8644
<P>Dear Reuben Helder,<BR /><BR />Kindly note that the below formula which I had given in the provious post, is an <STRONG>array formula</STRONG>.<BR /><STRONG>=INDEX($F$1:$F$6,SUM(COUNTIF($A1,"*"&$F$1:$F$6&"*")*ROW($F$1:$F$6)))</STRONG><BR /><BR />Array formulas are entered using <STRONG>Control+Shift+Enter</STRONG> instead of Enter.<BR /><BR />Vijaykumar Shetye<BR />Spreadsheet Excellence,<BR />Panaji, Goa, India</P>Sat, 13 May 2017 19:16:40 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69744#M8644vijaykumar shetye2017-05-13T19:16:40ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69750#M8645
<P>Vijaykumar Shetye, my formula does not need amending.</P><P>And your change from PI() to 1 will possibly give wrong results.</P><P> </P><P> </P><P> </P>Sat, 13 May 2017 20:09:56 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69750#M8645Detlef Lewin2017-05-13T20:09:56ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69766#M8646
<P>Dear Detlef Lewin,</P><P>Thanks for the reply and the correction. What is the significance of using Pi() in the formula?</P><P> </P><P>Viaykumar Shetye,</P><P>Spreadsheet Excellence,</P><P>Panaji, Goa, India</P>Sun, 14 May 2017 05:37:27 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69766#M8646vijaykumar shetye2017-05-14T05:37:27ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69771#M8647
<P>PI() itself is just a little gag. The lookup value has to be a number greater than 1. So 1.1 would be enough or 2 if you prefer whole numbers.</P><P> </P>Sun, 14 May 2017 07:47:04 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69771#M8647Detlef Lewin2017-05-14T07:47:04ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69778#M8648
<P>Hi<SPAN class=""> Reuben Helder,</SPAN></P><P> </P><P><SPAN class="">Good day,</SPAN></P><P> </P><P><SPAN class="">Please refer below formula to add more variable.</SPAN></P><P><SPAN class="">If statment is false then I have considered criteria is "NO MATCH"</SPAN></P><P><SPAN class="">IF(B3="MECH","MECHANICAL",IF(B3="ARCH","ARCHITECTURE",IF(B3="SALES","SALES",IF(B3="ELE","ELECTRICAL","NO MATCH"))))</SPAN></P><P> </P><P><SPAN class="">Is this helful for you? please revert bach.</SPAN></P><P> </P><P> </P><P><SPAN class="">Thanks & regards,</SPAN></P><P><SPAN class="">Manoj P.</SPAN></P><P> </P><P> </P><P> </P>Sun, 14 May 2017 11:54:31 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69778#M8648manoj patgar2017-05-14T11:54:31ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69779#M8649
<P>Hi Manoj,</P><P> </P><P>It's worth to re-read task requirements and previous posts, your formula is about something different.</P>Sun, 14 May 2017 12:10:19 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69779#M8649Sergei Baklan2017-05-14T12:10:19ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69797#M8650
<P>Dear Manoj Patgar,</P><P>(1) The problem has been posted on the forum by Reuben Helder because the formula using nested If functions is not adequate, due to its limitations.</P><P>The number of items in the text list is large (Sales, Arch, Land, ALL, Contracts, Construction and possibly a couple more), which nested IFs cannot handle. So the same formula with minor changes cannot work. The formula to be used has to be disigned to handle a long list of data.</P><P> </P><P>(2) The data which is being searched is not <STRONG>MECH</STRONG> or <STRONG>ARCH</STRONG>. It will be like Searching <STRONG>MECH</STRONG> or <STRONG>ARCH</STRONG> within a data list that contain entries like 'ABC <STRONG>MECH</STRONG>ANICAL ENGINEERS' or 'XYX <STRONG>ARCH</STRONG>ITECTS'. Hence the wild card character (<STRONG>*</STRONG>) has been used.</P><P> </P><P>Is it clear now?</P><P> </P><P>Vijaykumar Shetye,</P><P>Spreadsheet Excellence,</P><P>Panaji, Goa India</P><P> </P><P> </P><P> </P>Sun, 14 May 2017 16:31:21 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69797#M8650vijaykumar shetye2017-05-14T16:31:21ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69802#M8651
<P>I'm sorry but i see no limitations which could prevent to use nested IF. The only point it is a bit less flexible compare to nested If equivalents.</P><P> </P><P>But what to use depends on goals, in some cases quick hardcording works quite fine.</P><P> </P><P>If use formatting nested if becomes much more clear and editable. Like this</P><PRE>=IF(ISNUMBER(SEARCH("Sales", B3,1)),"Sales",<BR /> IF(ISNUMBER(SEARCH("Arch", B3,1)),"Architecture",<BR /> IF(ISNUMBER(SEARCH("Land", B3,1)),"Land",<BR /> IF(ISNUMBER(SEARCH("All", B3,1)),"All",<BR /> IF(ISNUMBER(SEARCH("Contracts", B3,1)),"Contracts",<BR /> IF(ISNUMBER(SEARCH("Construction", B3,1)),"Construction",<BR /> "No Match"<BR /> ))))))<BR /><BR /> </PRE><P>If instead of hardcoded strings use references nested IF becomes more flexible. And if add some extra references (nested if) for future strings to find it becomes even more flexible.</P><P> </P><PRE> =IF(ISNUMBER(SEARCH($F$1,B3,1)),$G$1,
IF(ISNUMBER(SEARCH($F$2,B3,1)),$G$2,
IF(ISNUMBER(SEARCH($F$3,B3,1)),$G$3,
IF(ISNUMBER(SEARCH($F$4,B3,1)),$G$4,
IF(ISNUMBER(SEARCH($F$5,B3,1)),$G$5,
IF(ISNUMBER(SEARCH($F$6,B3,1)),$G$6,
IF(ISNUMBER(SEARCH($F$7,B3,1)),$G$7,
IF(ISNUMBER(SEARCH($F$8,B3,1)),$G$8,
IF(ISNUMBER(SEARCH($F$9,B3,1)),$G$9,
"No Match"
)))))))))
</PRE><P>I don't vote for nested IF, i would like to say where is no limitations here. What to use that's concrete person choice.</P>Sun, 14 May 2017 18:57:36 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/69802#M8651Sergei Baklan2017-05-14T18:57:36ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/70335#M8652
<P>Dear Mr. Sergi,</P><P> </P><P>Yes. You are absolutely right.</P><P> </P><P><it is worth to re-read the question></P><P> </P><P>I am a beginner in excel and I am always welcome one who gives valuable suggestion.</P><P> </P><P>With best regards,</P><P>Manoj.</P>Tue, 16 May 2017 15:21:54 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/70335#M8652manoj patgar2017-05-16T15:21:54ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/70337#M8653
<P>Dear Mr. Vijaykumar,</P><P> </P><P>Thank you and appreciate your support.</P><P>I am beginner and I am always welcome one who want to give valuable suggestions.</P><P> </P><P>Thanks with best regards,</P><P>Manoj.</P>Tue, 16 May 2017 15:11:49 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/70337#M8653manoj patgar2017-05-16T15:11:49ZUsing =IF formula to calculate spending on groceries
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/440439#M28825
<P>Hi I'm looking to create a formula to calculate how much I spend on groceries a year. For an example:</P><P> </P><P><FONT>=IF(B1</FONT><FONT>="nofrills","Loblaws","sobeys"</FONT></P><P><FONT>then display C1 (the $$)</FONT></P><P><FONT>otherwise false</FONT></P>Thu, 11 Apr 2019 19:48:12 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/440439#M28825scmallory2019-04-11T19:48:12ZRe: Using =IF formula to calculate spending on groceries
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/443541#M28853
<P><LI-USER uid="319636"></LI-USER> , that's a separate question, better to start new conversation with it from here.</P>
<P>Formula could be</P>
<PRE>=IF(SUM(--(B1={<SPAN>"nofrills","Loblaws","sobeys"</SPAN>})),C1)</PRE>
<P> </P>Fri, 12 Apr 2019 09:00:17 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/443541#M28853Sergei Baklan2019-04-12T09:00:17ZRe: Using =IF formula to calculate spending on groceries
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/444620#M28866
<P><LI-USER uid="521"></LI-USER>I'm having troubles posting in a new thread.</P><P> </P><P>I tested out the formula and I was hoping cells in column D it would spit out the value in column C for anywhere it says "Food Basic" in column B. So the attached Test document.</P><P>Thank you,</P><P> </P>Fri, 12 Apr 2019 12:53:38 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/444620#M28866scmallory2019-04-12T12:53:38ZRe: Using =IF formula to calculate spending on groceries
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/445426#M28877
<P><LI-USER uid="319636"></LI-USER> , to start new conversation go to the General Discussion as in my link and click</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 479px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/108434i032FCC66822AFB4C/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span> </P>
<P> </P>
<P>Formula doesn't work since in column B you have "food basics ", and you compare with "food basics". Due to space texts are different. Trim the cell like</P>
<PRE>=IF(SUM(--(TRIM(B1)={"nofrills","food basics","Loblaws","sobeys"})),C1)</PRE>Fri, 12 Apr 2019 14:33:49 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/445426#M28877Sergei Baklan2019-04-12T14:33:49ZRe: Using =IF formula to calculate spending on groceries
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/446086#M28890
<P><LI-USER uid="521"></LI-USER>Thanks it worked!</P>Fri, 12 Apr 2019 15:40:17 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/446086#M28890scmallory2019-04-12T15:40:17ZRe: Using =IF formula to calculate spending on groceries
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/446127#M28893
<P><LI-USER uid="319636"></LI-USER> , good, thank you for the confirmation</P>Fri, 12 Apr 2019 15:47:04 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/446127#M28893Sergei Baklan2019-04-12T15:47:04ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/624401#M31415
Not OP, but this was exactly what I was looking for, thank you!Tue, 21 May 2019 19:01:15 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/624401#M31415Godfreyan2019-05-21T19:01:15ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/650706#M31755
<P><LI-USER uid="521"></LI-USER> </P><P> </P><P>I am looking for a formula to do the following. I believe it is an "IF", but not sure. I basically want a formula to return results based on the following:</P><P> </P><P>If the cell with the number is <=50, then multiply by 2</P><P>If the cell with the number is >50, but <=100, then multiply by 1.75</P><P>If the cell with the number is >100, but <=200, then multiply by 1.50</P><P> </P><P>I am trying to combine in one formula so that I can copy it all the way down the spreadsheet. Any help would be greatly appreciated.</P><P> </P><P> </P>Mon, 27 May 2019 18:10:20 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/650706#M31755bengal10222019-05-27T18:10:20ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/808020#M38381
<P><LI-USER uid="1639"></LI-USER> Thank you so much - I spent so long looking for this - it works perfectly!</P>Fri, 16 Aug 2019 10:40:04 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/808020#M38381Shannon-0_Green2019-08-16T10:40:04ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/808058#M38384
<P> </P>
<LI-CODE lang="markup">=A1*IF(A1>200,1,IF(A1>100,1.5,IF(A1>50,1.75,2)))
or
=A1*LOOKUP(A1,{0,50.000001,100.000001,200.000001},{2,1.75,1.5,1})</LI-CODE>
<P><LI-USER uid="349610"></LI-USER> </P>Fri, 16 Aug 2019 11:13:57 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/808058#M38384Sergei Baklan2019-08-16T11:13:57ZRe: Using =IF formula to calculate spending on groceries
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/958263#M42934
<P><LI-USER uid="521"></LI-USER> this is exactly what I`m looking for but excel gives me an error - attached. </P><P>Could you please help? </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="1.JPG" style="width: 999px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/151469i2F23D4631C0B57B6/image-size/large?v=v2&px=999" role="button" title="1.JPG" alt="1.JPG" /></span></P>Mon, 28 Oct 2019 08:30:52 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/958263#M42934krisi0422019-10-28T08:30:52ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1083613#M47459
<P><LI-USER uid="1639"></LI-USER> This works perfectly in most situations and is super simple, so I want to thank you for providing this! I haven't seen this solution offered anywhere else! The only problem I sometimes run into is that this isn't an exact match, so if I have a list of text that has some of the same terms or letter combinations, it sometimes returns the wrong thing. Is there a way to make this exact match only?</P>Mon, 30 Dec 2019 18:40:33 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1083613#M47459Godfreyan2019-12-30T18:40:33ZRe: Using =IF formula to calculate spending on groceries
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1083647#M47462
<P><LI-USER uid="434576"></LI-USER> </P>
<P>Could you please attache the file instead of screenshort.</P>Mon, 30 Dec 2019 19:00:58 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1083647#M47462Sergei Baklan2019-12-30T19:00:58ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1166160#M51969
<P><LI-USER uid="521"></LI-USER> I really like your solution with nested formatting, though I was wondering if there is a way to efficiently do a few hundred words?</P><P> </P><P>There must be a better option than typing in all the words</P>Tue, 11 Feb 2020 11:06:18 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1166160#M51969Freek_Olivier2020-02-11T11:06:18ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1166848#M52019
<P><LI-USER uid="553134"></LI-USER> </P>
<P>I gave nested IF only to illustrate that is workable. Formula which <LI-USER uid="1639"></LI-USER> suggested shall work perfectly, especially on big ranges.</P>Tue, 11 Feb 2020 15:48:23 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1166848#M52019Sergei Baklan2020-02-11T15:48:23ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1318951#M59271
<P><LI-USER uid="44810"></LI-USER> I am working on a spreadsheet that contains macros. In column A, users input an 'X' to make that row active which then gets copied for an email. If the row does not contain an 'X', the row is hidden and not copied for an email. </P><P> </P><P>So what I am trying to accomplish: </P><P> </P><P>IF a range of cells don't contain an 'X', THEN display "general text message".</P><P> </P><P>If none of the cells from A2 through A10 contain an 'X', then I want a message to be displayed in B1. However, if any of the cells from A2 though A10 contain an 'X', then B2 should not display a message.</P><P> </P><P>If there is a way to accomplish this, is it possible to have the formula automatically adjust to a new range of cells, ie. if I were to add a row between A2 and A10.</P><P> </P><P> </P>Sun, 19 Apr 2020 06:02:12 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1318951#M59271PatrickG12342020-04-19T06:02:12ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1394819#M62594
<P>I have a problemm where I need to filter out the state and district name from the address column and place the same in the adjoining column</P><P> </P><P>State llist can be arranged as a range. But what would be the formula to use and how will it be used to math the address column </P>Sat, 16 May 2020 06:39:56 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1394819#M62594ZillaSainiKWelfareThan2020-05-16T06:39:56ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1416785#M63554
<P><LI-USER uid="1639"></LI-USER> </P><P> </P><P>Hi was googling for a solution to the subject of above thread and found and used your solution,</P><P><SPAN>[Key words in column F, returned text in column G, text to check in A1.</SPAN></P><P><STRONG>=LOOKUP(PI(),1/COUNTIF(A1,"*"&$F$1:$F$6&"*"),$G$1:$G$6) </STRONG>]with 1 problem; the keyword found doesn't display.</P><P>using Excel 2010</P><P>would appreciate any help </P><P> </P><P>thanks!</P><P> </P>Mon, 25 May 2020 22:06:49 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1416785#M63554Sanduke2020-05-25T22:06:49ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1420878#M63748
<P><LI-USER uid="679297"></LI-USER> </P><P>Could you upload your file?</P><P> </P>Wed, 27 May 2020 15:18:57 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1420878#M63748Detlef Lewin2020-05-27T15:18:57ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1853618#M80056
Is there an equivalent way to use this Excel logic in Power Query were the source list of text string is a table or another query?Wed, 04 Nov 2020 15:35:45 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1853618#M80056DMColleran2020-11-04T15:35:45ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1854446#M80071
<P><LI-USER uid="857434"></LI-USER> </P>
<P>Not sure what exactly you'd like to do, perhaps something like</P>
<LI-CODE lang="powerquery">= Table.SelectRows(
#"Table To Filter" ,
each List.Contains(
#"Table With Filter"[ColumnWithValues],
[ColumnOnWhichWeFilterSourceTable]
)
)</LI-CODE>
<P> </P>Wed, 04 Nov 2020 18:52:59 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1854446#M80071Sergei Baklan2020-11-04T18:52:59ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1854646#M80078
<P><LI-USER uid="521"></LI-USER> we have a data cleanliness issue, whereby a freeform field contains key information about each entry. Inside the freeform field are keywords which we want to pull out into a new column. The <EM>=LOOKUP(PI(),1/COUNIF(TargetCell,"*"&KeywordRange&"*"),KeywordRange) </EM>Formula works well in an Excel sheet, however our dataset is much larger and we will need to handle it in PQ or some other tool. Essentially we want perform the same operation as the above function but within PQ. </P><P> </P><P>I have attached a sample of the dataset, with tables showing the raw data and target keywords (INPUTS) as well as a sample output of cleaned data (OUTPUT). I achieved the example output by using a fuzzy match in PQ, but that is not necessarily a perfect resolution. Perhaps it works. </P><P> </P><P>Cheers, </P>Wed, 04 Nov 2020 19:58:34 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1854646#M80078DMColleran2020-11-04T19:58:34ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1854882#M80083
<P><LI-USER uid="857434"></LI-USER> </P>
<P>I' not sure how the table with keywords works. For example, keyword <STRONG>a</STRONG> is met in every word for several times. Thus simply try to reproduce what LOOKUP() do. Second part of your query, instead of fuzzy merging, is </P>
<LI-CODE lang="powerquery"> #"Lowercased Text" = Table.TransformColumns(
#"Removed Columns",
{{"Description", Text.Lower, type text}}
),
KeywordsTable = CannaFormKeywords,
#"Add Cannabis Form" = Table.AddColumn(
#"Lowercased Text",
"Cannabis Form",
each
let
txt = [Description],
lst = KeywordsTable[Keyword],
out = KeywordsTable[Cannabis Form],
transformed = List.Transform(
lst,
each
if Text.Contains(txt, _)
then out{List.PositionOf(lst,_)}
else null
),
cleaned =
List.Last(
List.RemoveNulls(
transformed
)
)
in
cleaned),
#"Remove source texts" = Table.SelectColumns(
#"Add Cannabis Form",
{"ID", "Date", "Cannabis Form"}
)
in
#"Remove source texts"</LI-CODE>
<P>(see duplicated query). </P>
<P>Result looks the same as with fuzzy match</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 599px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/231525i96F17AD38D4CE408/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P> </P>
<P> </P>
<P> </P>Wed, 04 Nov 2020 21:06:13 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1854882#M80083Sergei Baklan2020-11-04T21:06:13ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1978307#M83483
<P><LI-USER uid="521"></LI-USER> </P><LI-CODE lang="excel">=IFERROR(INDEX($G$1:$G$6,AGGREGATE(15,3,SEARCH($G$1:$G$6,A1)*ROW($G$1:$G$6),1),1), "No Match")
* AGGREGATE can ignore error</LI-CODE>Fri, 11 Dec 2020 01:29:21 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1978307#M83483Willy Lau2020-12-11T01:29:21ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1978658#M83485
<P><LI-USER uid="97717"></LI-USER> </P>
<P>Sorry, I didn't catch what's that about. If about recent post, I'd prefer formula which <LI-USER uid="857434"></LI-USER> used before</P>
<LI-CODE lang="excel">=LOOKUP(PI(),1/COUNTIF(TargetCell,"*"&KeywordRange&"*"),CannabisRange)</LI-CODE>
<P>it works better than SEARCH() within AGGREGATE().</P>
<P>As for the concrete formula, it won't work for the sample from previous post, shall be adjusted.</P>Fri, 11 Dec 2020 07:37:35 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/1978658#M83485Sergei Baklan2020-12-11T07:37:35ZRe: HOW TO: "If cell contains specific text then return specific text"
https://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/2329684#M98898
<P><LI-USER uid="53876"></LI-USER> </P><P>I agree and your value add help me no end. Thanks and keep up the great dialogue.</P>Thu, 06 May 2021 04:36:32 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-quot-if-cell-contains-specific-text-then-return-specific/m-p/2329684#M98898onderay2021-05-06T04:36:32Z