Verify if cell does and does not contain a specific word

[jomilo]

Excel Gurus,

I have a dropdown list in cell A1 with four possible choices:
Statement 1: "50 gallons - White"
Statement 2: "50 gallons - Black"
Statement 3: "25 gallons - Green"
Statement 4: "25 gallons - Blue"

In cell B1 I need to evaluate what was choosen in cell A1 to determine what action to take.

So, how do I tell cell B1 "if A1 does contain the word/number "25" AND does NOT contain the word "Blue" then make B1 equal to cell C1?"

I can get this to work to verify if the cell contains a specific word, but I can't figure out how to combine the AND statement with the ISNUMBER, SEARCH, and NOT statements to also verify if the same cell does NOT contain a certain word. Any help would be greatly appreciated.

Thanks, jomilo

 

[scottylad2]

This will give you trues and Falses, adjust to suit.

Excel Workbook
	F	G
24	50 Gallons - White	FALSE
25	50 Gallons - Black	FALSE
26	25 Gallons - Green	FALSE
27	25 Gallons - Blue	TRUE
Sheet4

Also, you could use the double -- to change to 1 & 0 if that suits your setup better

Excel Workbook
	F	G
24	50 Gallons - White	0
25	50 Gallons - Black	0
26	25 Gallons - Green	0
27	25 Gallons - Blue	1
Sheet4

 

[jomilo]

I could be missing something about "scottylab2's" response (I obviously need the help), but I don't think the response(s) really answer my question. I need to verify in cell B1 that cell A1 DOES contain "25" and at the same time DOES NOT contain "Blue." Then, and only if those two conditions are TRUE, cell B1 needs to equal cell C1, otherwise cell B1 is to equal 0 (zero).

Here is the formula I have placed in cell B1, which doesn't work:
=IF(AND(ISNUMBER(SEARCH("25",A1))), IF(ISERR(FIND("Blue", A1)))), C1, 0)

Condition 1: =IF(AND(ISNUMBER(SEARCH("25",A1)))
Condition 2: IF(ISERR(FIND("Blue", A1))))
Result if Condition 1 and Condition 2 are true: C1 (B1 will equal C1)
Result if Condition 1 and Condition 2 are false: 0 (B1 will equal zero)

Also relevant is that I'm not looking for the entire cell to be "25" and/or "Blue," only to contain those string fragments.

Any additonal help would be greatly appreciated. Sorry if I'm just not getting it.

Thanks, jomilo

 

[T. Valko]

I could be missing something about "scottylab2's" response (I obviously need the help), but I don't think the response(s) really answer my question. I need to verify in cell B1 that cell A1 DOES contain "25" and at the same time DOES NOT contain "Blue." Then, and only if those two conditions are TRUE, cell B1 needs to equal cell C1, otherwise cell B1 is to equal 0 (zero).

Here is the formula I have placed in cell B1, which doesn't work:
=IF(AND(ISNUMBER(SEARCH("25",A1))), IF(ISERR(FIND("Blue", A1)))), C1, 0)

Condition 1: =IF(AND(ISNUMBER(SEARCH("25",A1)))
Condition 2: IF(ISERR(FIND("Blue", A1))))
Result if Condition 1 and Condition 2 are true: C1 (B1 will equal C1)
Result if Condition 1 and Condition 2 are false: 0 (B1 will equal zero)

Also relevant is that I'm not looking for the entire cell to be "25" and/or "Blue," only to contain those string fragments.

Any additonal help would be greatly appreciated. Sorry if I'm just not getting it.

Thanks, jomilo

Try this...

=AND(COUNT(FIND(25,A1)),COUNTIF(A1,"*blue*")=0)*C1

 

[jomilo]

#NAME?, I think we're closer, but there is something still being missed. I know it's in my explanation of what I'm looking for. I think I muddied the waters with my last post. You gave me what I asked for. The problem is I didn't really want what I asked for. (Everything makes more sense in my head. It's much harder to explain.) So I'm going to go back to my beginning post and start from there. I beg all of you for your indulgence.

Going back to my original post:

"In cell B1 I need to evaluate what was choosen in cell A1 to determine what action to take.

So, how do I tell cell B1 "if A1 does contain the word/number "25" AND does NOT contain the word "Blue" then make B1 equal to cell C1?"

In your formula, if both conditions are true, then cell B1 does return what is is in C1. If both conditions are not true, the cell is zero. That's what I asked for, but not what I meant to ask for. Sorry. I need cell B1 to equal C1 only if cell A1 does contain "25" but does NOT contain "Blue."

I hope that clears some things up and I'm sorry for the misunderstanding.

Jomilo

 

[T. Valko]

#NAME?, I think we're closer, but there is something still being missed. I know it's in my explanation of what I'm looking for. I think I muddied the waters with my last post. You gave me what I asked for. The problem is I didn't really want what I asked for. (Everything makes more sense in my head. It's much harder to explain.) So I'm going to go back to my beginning post and start from there. I beg all of you for your indulgence.

Going back to my original post:

"In cell B1 I need to evaluate what was choosen in cell A1 to determine what action to take.

So, how do I tell cell B1 "if A1 does contain the word/number "25" AND does NOT contain the word "Blue" then make B1 equal to cell C1?"

In your formula, if both conditions are true, then cell B1 does return what is is in C1. If both conditions are not true, the cell is zero. That's what I asked for, but not what I meant to ask for. Sorry. I need cell B1 to equal C1 only if cell A1 does contain "25" but does NOT contain "Blue."

I hope that clears some things up and I'm sorry for the misunderstanding.

Jomilo

Hmmm...

Well, now I'm

Let's assume C1 = 10

Can you post several examples of what might be in cell A1 and then next to it show us what result you expect (which would be either 10 or 0).

 

[snoopyhr]

here is my 2 cents on this... bit seems to be very simple, chances are I missed something...

Excel Workbook
	A	B	C	D
1	Your drop down List	Action taken	Value for C1
2	Statement 4: "25 gallons - Blue"	0	Value C1
3	Statement 2: "50 gallons - Black"	Value C2	Value C2
4	Statement 1: "50 gallons - White"	Value C3	Value C3
5	Statement 3: "25 gallons - Green"	Value C4	Value C4
6
7	Criteria given :
8	If selected item CONTAINS 25 but not "BLUE" then B1=C1 else , Zero.
Sheet1

 

[jomilo]

#NAME?,

(Also Checking out "snoopyhr's" comment, but wanted to get this back to you ASAP)

Going back to my original post, I have a dropdown list in cell A1 with four possible choices:

Statement 1: "50 gallons - White"
Statement 2: "50 gallons - Black"
Statement 3: "25 gallons - Green"
Statement 4: "25 gallons - Blue"

A. Let's assume the dropdown list goes in cell A1.
B. Let's assume that the formula we are working on goes in cell B1.
C. Let's assume cell C1 = 10.
D. Let's assume the formula needs to accomplish the following:
If A1 does contain the word/number "25" but does NOT contain the word "Blue", the B1 = 10.

Based on the above dropdown list and the four assumptions, here are some possible results:

1. If A1 contains the word/number "25" but does NOT contain the word "Blue" then B1 = 10.

2. If A1 contains the word/number "25" and also contains the word "Blue" then B1 = 0.

3. If A1 does NOT contain the word/number "25" but does contain the word "Blue" then B1 = 0.

4. If A1 does NOT contain the word/number "25" and does NOT contain the word "Blue" then B1 = 0.

Does that help?

Jomilo

 

[bryonwoods30]

if you only have those 4 choices in the drop down wouldn't this work since you only have 1 result that meets your criteria?


=IF(A1="25 gallons - Green",C1,0)

 

[T. Valko]

1. If A1 contains the word/number "25" but does NOT contain the word "Blue" then B1 = 10.

2. If A1 contains the word/number "25" and also contains the word "Blue" then B1 = 0.

3. If A1 does NOT contain the word/number "25" but does contain the word "Blue" then B1 = 0.

4. If A1 does NOT contain the word/number "25" and does NOT contain the word "Blue" then B1 = 0.

Ok, the formula I suggested returns all of those results based on all of those possible conditions.