From: Colin Hayes on 5 Jun 2010 17:07 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: Don Guillett on 5 Jun 2010 17:32 =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: "David Biddulph" groups [at] on 5 Jun 2010 17:42 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: Colin Hayes on 5 Jun 2010 17:47 In article , Don Guillett writes>=IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) > Hi Don OK Thanks for that. I can't quite get it to work though , for what I have in mind. I found that this works for a single search parameter : =IF(AND(COUNTIF(E2,"*1bx*"),COUNTIF(J2,"11")),1,J2) but of course it only looks for "*1bx*". I need in incorporate an OR expression to also search for "*2bx*" , "*3bx*" and "*4bx*" in the same formula. Any ideas how to work these other values in? Thanks for your help. Best Wishes From: Colin Hayes on 5 Jun 2010 18:02 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 >> >  |  Next  |  Last