From: Don Guillett on 5 Jun 2010 18:18 I did!!! -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "David Biddulph" wrote in message news:c5OdnVDaM7E0WZfRnZ2dnUVZ8uOdnZ2d(a)bt.com...>I assume that you intended =IF(AND(RIGHT(E2,2)="bx",j2=11),1,j2) ? > > "bx", not "1bx", as you've got 2, not 3, as the second parameter of the > right function? > -- > David Biddulph > > > "Don Guillett" wrote in message > news:eucSraPBLHA.5476(a)TK2MSFTNGP06.phx.gbl... >> =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) >> >> -- >> Don Guillett >> Microsoft MVP Excel >> SalesAid Software >> dguillett(a)gmail.com >> "Colin Hayes" wrote in message >> news:WY5OXLAGyrCMFwoB(a)chayes.demon.co.uk... >>> >>> HI >>> >>> I need to express something in a formula , and am having trouble with >>> it. >>> >>> I need to say this : >>> >>> IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 , >>> OTHERWISE PUT J2 >>> >>> Can someone assist with some code to make this happen , please? >>> >>> Grateful for any help. Sorry for double post. >>> >>> >>> >>> Best Wishes >> > From: T. Valko on 5 Jun 2010 22:02 >The formula would need to look specifically>for any of the 4 phrases ("*1bx*" OR "*2bx*" >OR "*3bx*" OR "*4bx*") contained anywhere in E2. Try this... =IF(COUNT(SEARCH({1,2,3,4}&"BX",E2))*(J2=11),1,J2) -- Biff Microsoft Excel MVP "Colin Hayes" wrote in message news:Gfx6WVAmlsCMFw6b(a)chayes.demon.co.uk...> In article , David Biddulph > writes >>I assume that you intended =IF(AND(RIGHT(E2,2)="bx",j2=11),1,j2) ? >> >>"bx", not "1bx", as you've got 2, not 3, as the second parameter of the >>right function? >>-- >>David Biddulph > > Hi David > > Well no , not quite. > > The formula would need to look specifically for any of the 4 phrases ( > "*1bx*" OR "*2bx*" OR "*3bx*" OR "*4bx*") contained anywhere in E2. > > If it finds any of these , and J2=11 , then put 1. If it doesn't find any > of these , put J2. > > I would be dragging this down , so it would look in lower Cells in E also. > > There are other phrases containing the 'bx' suffix in E2 which I would > want it to ignore. So '5bx' , '6bx' would be ignored for example. For this > reason , it's not enough just to find 'bx'. > > I wouldn't want the issued clouded by the fact that the expression > contains similar letters. The formula would need to identify them > discretely , in the same way as if it were looking for pink , blue , green > or yellow. > > Thanks for your help. > > >> > > >> >>"Don Guillett" wrote in message >>news:eucSraPBLHA.5476(a)TK2MSFTNGP06.phx.gbl... >>> =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) >>> >>> -- >>> Don Guillett >>> Microsoft MVP Excel >>> SalesAid Software >>> dguillett(a)gmail.com >>> "Colin Hayes" wrote in message >>> news:WY5OXLAGyrCMFwoB(a)chayes.demon.co.uk... >>>> >>>> HI >>>> >>>> I need to express something in a formula , and am having trouble with >>>> it. >>>> >>>> I need to say this : >>>> >>>> IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT >>1 , >>>> OTHERWISE PUT J2 >>>> >>>> Can someone assist with some code to make this happen , please? >>>> >>>> Grateful for any help. Sorry for double post. >>>> >>>> >>>> >>>> Best Wishes >>> >> > From: Stan Brown on 6 Jun 2010 07:51 On Sat, 5 Jun 2010 22:07:18 +0100, Colin Hayes wrote:> I need to say this : > > IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 , Then why don't you read the numerous responses that were posted in microsoft.public.excel.misc (including one from me)? Please do not post the same question multiple times. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... From: Colin Hayes on 6 Jun 2010 10:35 In article , T. Valko writes>>The formula would need to look specifically >>for any of the 4 phrases ("*1bx*" OR "*2bx*" >>OR "*3bx*" OR "*4bx*") contained anywhere in E2. > >Try this... > >=IF(COUNT(SEARCH({1,2,3,4}&"BX",E2))*(J2=11),1,J2) > HI Biff Yes , that's got it. Perfect first time. Thanks for your time and expertise. Best Wishes From: T. Valko on 6 Jun 2010 12:05 You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Colin Hayes" wrote in message news:p4LB\$bANJ7CMFwMj(a)chayes.demon.co.uk...> In article , T. Valko > writes >>>The formula would need to look specifically >>>for any of the 4 phrases ("*1bx*" OR "*2bx*" >>>OR "*3bx*" OR "*4bx*") contained anywhere in E2. >> >>Try this... >> >>=IF(COUNT(SEARCH({1,2,3,4}&"BX",E2))*(J2=11),1,J2) >> > > > HI Biff > > Yes , that's got it. Perfect first time. > > Thanks for your time and expertise. > > > > Best Wishes First  |  Prev  |