Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find part of a word in cell
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find part of a word in cell
try
=IF(ISNUMBER(SEARCH("book",B12,1)),C12,0) "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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find part of a word in cell
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find part of a word in cell
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. "Duke Carey" wrote: try =IF(ISNUMBER(SEARCH("book",B12,1)),C12,0) "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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find part of a word in cell
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find part of a word in cell
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find part of a word in cell
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. "Ron Coderre" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete cell that doesn't contain a specific word | Excel Discussion (Misc queries) | |||
Paste from Word table so that 1 table cell = 1 Excel cell | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
How do I find the cell address of the 2nd largest of a set? | Excel Discussion (Misc queries) | |||
Can you make a cell with a diagonal, so that you can edit the upper and lower part of the cell? | New Users to Excel |