June 6th, 2010, 03:02 AM
posted to microsoft.public.excel.worksheet.functions
|
|
Complex AND OR Formula
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
...
In article , David Biddulph
groups@[at] 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
...
=IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Colin Hayes" wrote in message
...
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
|