# Verify if cell does and does not contain a specific word

#### jomilo

##### New Member
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

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### scottylad2

##### Well-known Member
This will give you trues and Falses, adjust to suit.

Excel Workbook
FG
2450 Gallons - WhiteFALSE
2550 Gallons - BlackFALSE
2625 Gallons - GreenFALSE
2725 Gallons - BlueTRUE
Sheet4

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

Excel Workbook
FG
2450 Gallons - White0
2550 Gallons - Black0
2625 Gallons - Green0
2725 Gallons - Blue1
Sheet4

#### jomilo

##### New Member
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

##### Well-known Member
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

##### New Member
#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

##### Well-known Member
#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

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

Excel Workbook
ABCD
1Your drop down ListAction takenValue for C1
2Statement 4: "25 gallons - Blue"0Value C1
3Statement 2: "50 gallons - Black"Value C2Value C2
4Statement 1: "50 gallons - White"Value C3Value C3
5Statement 3: "25 gallons - Green"Value C4Value C4
6
7Criteria given :
8If selected item CONTAINS 25 but not "BLUE" then B1=C1 else , Zero.
Sheet1

#### jomilo

##### New Member
#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

Last edited:

#### bryonwoods30

##### Board Regular
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

##### Well-known Member
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.

Replies
3
Views
665
Replies
4
Views
179
Replies
2
Views
229
Replies
6
Views
143
Replies
0
Views
156

Threads
1,191,576
Messages
5,987,390
Members
440,095
Latest member
yanaungmyint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back