Discussion Re: 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/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 GMTvijaykumar shetye2017-05-14T05:37:27ZHOW 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:15Z