software

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!

try

=IF(ISNUMBER(SEARCH(quot;bookquot;,B12,1)),C12,0)

quot;Elainequot; wrote:

gt; In col B I have some entries many of which have the word 'book' in some form
gt; -- eg. notebook or bookmark.
gt;
gt; In col C I have some numeric codes -- 1, 2, 3 etc.
gt;
gt; If a cell in Col B contains the word 'book' I would like in Col D to show
gt; what is in Col C; if the cell in Col B does not contain the word 'book', I
gt; would like to put a 0 (zero) in Col D.
gt;
gt; Thanks for your help!

Perhaps something like this:

For values in A1 and B1

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

Does that help?

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

XL2002, WinXP-Proquot;Elainequot; wrote:

gt; In col B I have some entries many of which have the word 'book' in some form
gt; -- eg. notebook or bookmark.
gt;
gt; In col C I have some numeric codes -- 1, 2, 3 etc.
gt;
gt; If a cell in Col B contains the word 'book' I would like in Col D to show
gt; what is in Col C; if the cell in Col B does not contain the word 'book', I
gt; would like to put a 0 (zero) in Col D.
gt;
gt; Thanks for your help!

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.

quot;Duke Careyquot; wrote:

gt; try
gt;
gt; =IF(ISNUMBER(SEARCH(quot;bookquot;,B12,1)),C12,0)
gt;
gt; quot;Elainequot; wrote:
gt;
gt; gt; In col B I have some entries many of which have the word 'book' in some form
gt; gt; -- eg. notebook or bookmark.
gt; gt;
gt; gt; In col C I have some numeric codes -- 1, 2, 3 etc.
gt; gt;
gt; gt; If a cell in Col B contains the word 'book' I would like in Col D to show
gt; gt; what is in Col C; if the cell in Col B does not contain the word 'book', I
gt; gt; would like to put a 0 (zero) in Col D.
gt; gt;
gt; gt; Thanks for your help!

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,quot;*book*quot;),C1,0)

I hope that helps?

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

XL2002, WinXP-Proquot;Ron Coderrequot; wrote:

gt; Perhaps something like this:
gt;
gt; For values in A1 and B1
gt;
gt; C1: =IF(COUNTIF(B1,quot;*book*quot;),C1,0)
gt; Copy down as far as needed.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Elainequot; wrote:
gt;
gt; gt; In col B I have some entries many of which have the word 'book' in some form
gt; gt; -- eg. notebook or bookmark.
gt; gt;
gt; gt; In col C I have some numeric codes -- 1, 2, 3 etc.
gt; gt;
gt; gt; If a cell in Col B contains the word 'book' I would like in Col D to show
gt; gt; what is in Col C; if the cell in Col B does not contain the word 'book', I
gt; gt; would like to put a 0 (zero) in Col D.
gt; gt;
gt; gt; Thanks for your help!

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

quot;Ron Coderrequot; wrote:

gt; UM...how about i give a formula that actually works and does what you want.
gt;
gt; For text in B1 and a number in C1
gt;
gt; D1: =IF(COUNTIF(B1,quot;*book*quot;),C1,0)
gt;
gt; I hope that helps?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Perhaps something like this:
gt; gt;
gt; gt; For values in A1 and B1
gt; gt;
gt; gt; C1: =IF(COUNTIF(B1,quot;*book*quot;),C1,0)
gt; gt; Copy down as far as needed.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Elainequot; wrote:
gt; gt;
gt; gt; gt; In col B I have some entries many of which have the word 'book' in some form
gt; gt; gt; -- eg. notebook or bookmark.
gt; gt; gt;
gt; gt; gt; In col C I have some numeric codes -- 1, 2, 3 etc.
gt; gt; gt;
gt; gt; gt; If a cell in Col B contains the word 'book' I would like in Col D to show
gt; gt; gt; what is in Col C; if the cell in Col B does not contain the word 'book', I
gt; gt; gt; would like to put a 0 (zero) in Col D.
gt; gt; gt;
gt; gt; gt; Thanks for your help!

Regarding your second request(s):To only match if the cell contains BOTH quot;bookquot; AND quot;paperquot;

See if one of these works for you:

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

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

Does that help?

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

XL2002, WinXP-Proquot;Elainequot; wrote:

gt; Thanks, Ron. Works beautifully! What you and Duke have provided is going to
gt; be very useful!
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; UM...how about i give a formula that actually works and does what you want.
gt; gt;
gt; gt; For text in B1 and a number in C1
gt; gt;
gt; gt; D1: =IF(COUNTIF(B1,quot;*book*quot;),C1,0)
gt; gt;
gt; gt; I hope that helps?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Perhaps something like this:
gt; gt; gt;
gt; gt; gt; For values in A1 and B1
gt; gt; gt;
gt; gt; gt; C1: =IF(COUNTIF(B1,quot;*book*quot;),C1,0)
gt; gt; gt; Copy down as far as needed.
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Elainequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; In col B I have some entries many of which have the word 'book' in some form
gt; gt; gt; gt; -- eg. notebook or bookmark.
gt; gt; gt; gt;
gt; gt; gt; gt; In col C I have some numeric codes -- 1, 2, 3 etc.
gt; gt; gt; gt;
gt; gt; gt; gt; If a cell in Col B contains the word 'book' I would like in Col D to show
gt; gt; gt; gt; what is in Col C; if the cell in Col B does not contain the word 'book', I
gt; gt; gt; gt; would like to put a 0 (zero) in Col D.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks for your help!

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.

quot;Ron Coderrequot; wrote:

gt; Regarding your second request(s):
gt;
gt;
gt; To only match if the cell contains BOTH quot;bookquot; AND quot;paperquot;
gt;
gt; See if one of these works for you:
gt;
gt; D12: =IF(SUMPRODUCT(COUNTIF(B12,{quot;*paper*quot;,quot;*book*quot;})*{ 1,2})=3,C12,0)
gt; OR
gt; D12: =IF(AND(COUNTIF(B12,quot;*paper*quot;),COUNTIF(B12,quot;*book* quot;)),C12,0)
gt;
gt; If you want to match if the cell contains EITHER quot;bookquot; OR quot;paperquot;
gt; Try this
gt; D12: =IF(SUMPRODUCT(COUNTIF(B12,{quot;*paper*quot;,quot;*book*quot;})), C12,0)
gt; OR
gt; D12: =IF(OR(COUNTIF(B12,quot;*paper*quot;),COUNTIF(B12,quot;*book*quot; )),C12,0)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Elainequot; wrote:
gt;
gt; gt; Thanks, Ron. Works beautifully! What you and Duke have provided is going to
gt; gt; be very useful!
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; UM...how about i give a formula that actually works and does what you want.
gt; gt; gt;
gt; gt; gt; For text in B1 and a number in C1
gt; gt; gt;
gt; gt; gt; D1: =IF(COUNTIF(B1,quot;*book*quot;),C1,0)
gt; gt; gt;
gt; gt; gt; I hope that helps?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Perhaps something like this:
gt; gt; gt; gt;
gt; gt; gt; gt; For values in A1 and B1
gt; gt; gt; gt;
gt; gt; gt; gt; C1: =IF(COUNTIF(B1,quot;*book*quot;),C1,0)
gt; gt; gt; gt; Copy down as far as needed.
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Elainequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; In col B I have some entries many of which have the word 'book' in some form
gt; gt; gt; gt; gt; -- eg. notebook or bookmark.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; In col C I have some numeric codes -- 1, 2, 3 etc.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If a cell in Col B contains the word 'book' I would like in Col D to show
gt; gt; gt; gt; gt; what is in Col C; if the cell in Col B does not contain the word 'book', I
gt; gt; gt; gt; gt; would like to put a 0 (zero) in Col D.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks for your help!