Find part of a word in cell

[Elaine]

In col B I have some entries many of which have the word 'book' in some form
-- eg. notebook or bookmark.

In col C I have some numeric codes -- 1, 2, 3 etc.

If a cell in Col B contains the word 'book' I would like in Col D to show
what is in Col C; if the cell in Col B does not contain the word 'book', I
would like to put a 0 (zero) in Col D.

Thanks for your help!

 

[Duke Carey]

try

=IF(ISNUMBER(SEARCH("book",B12,1)),C12,0)

 

[Ron Coderre]

Perhaps something like this:

For values in A1 and B1

C1: =IF(COUNTIF(B1,"*book*"),C1,0)
Copy down as far as needed.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Elaine]

Thank you, Duke! I hope that you don't mind if I have a follow-up question.
You answered the question I have but I was just trying to play with your
formula and couldn't get this.

If I was looking for two terms -- 'book' and 'paper' how would I amend the
formula that you have provided me. I need help in two areas:

If either one of 'paper' or 'book' was contained in B12, D12 should get the
code from c12.

Second D12 should get the code from C12 only if BOTH words are in B12.

Thanks again and I hope that you don't mind answering these two questions as
I am it will come up sooner or later.

 

[Ron Coderre]

UM...how about i give a formula that actually works and does what you want.

For text in B1 and a number in C1

D1: =IF(COUNTIF(B1,"*book*"),C1,0)

I hope that helps?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Elaine]

Thanks, Ron. Works beautifully! What you and Duke have provided is going to
be very useful!

 

[Ron Coderre]

Regarding your second request(s):


To only match if the cell contains BOTH "book" AND "paper"

See if one of these works for you:

D12: =IF(SUMPRODUCT(COUNTIF(B12,{"*paper*","*book*"})*{1,2})=3,C12,0)
OR
D12: =IF(AND(COUNTIF(B12,"*paper*"),COUNTIF(B12,"*book*")),C12,0)

If you want to match if the cell contains EITHER "book" OR "paper"
Try this
D12: =IF(SUMPRODUCT(COUNTIF(B12,{"*paper*","*book*"})),C12,0)
OR
D12: =IF(OR(COUNTIF(B12,"*paper*"),COUNTIF(B12,"*book*")),C12,0)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Elaine]

Thank you very much for your help. I used the countif versions in your reply
and they both work. I really appreciate the time and effort.