Find part of a word in cell
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" wrote:
Thanks, Ron. Works beautifully! What you and Duke have provided is going to
be very useful!
"Ron Coderre" wrote:
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
"Ron Coderre" wrote:
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" wrote:
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!
|