Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula to identify a keyword in all cells of a column
I have a spreadsheet with about 5,000 rows andjust one column - column A. I
would like to record a formula in column b where for every cell in column A that contains the word "web" somewhere in the description (the description could have up to 10 words), it will place the number "1" in column B. Can this be done? I assume that it is some sort of if/then statement, but when I try =IF(A2="*web*",1,0) with the wild card "*" it does not seem to work. Any ideas? Thank you, Paul |
#2
|
|||
|
|||
=isnumber(find("web",a1))*1
"HTC" wrote in message ... I have a spreadsheet with about 5,000 rows andjust one column - column A. I would like to record a formula in column b where for every cell in column A that contains the word "web" somewhere in the description (the description could have up to 10 words), it will place the number "1" in column B. Can this be done? I assume that it is some sort of if/then statement, but when I try =IF(A2="*web*",1,0) with the wild card "*" it does not seem to work. Any ideas? Thank you, Paul |
#3
|
|||
|
|||
Hi Paul
One way =IF(ISNUMBER(SEARCH("web",A2)),1,0) -- Regards Roger Govier "HTC" wrote in message ... I have a spreadsheet with about 5,000 rows andjust one column - column A. I would like to record a formula in column b where for every cell in column A that contains the word "web" somewhere in the description (the description could have up to 10 words), it will place the number "1" in column B. Can this be done? I assume that it is some sort of if/then statement, but when I try =IF(A2="*web*",1,0) with the wild card "*" it does not seem to work. Any ideas? Thank you, Paul |
#4
|
|||
|
|||
Thank you very much for both replies.
"HTC" wrote: I have a spreadsheet with about 5,000 rows andjust one column - column A. I would like to record a formula in column b where for every cell in column A that contains the word "web" somewhere in the description (the description could have up to 10 words), it will place the number "1" in column B. Can this be done? I assume that it is some sort of if/then statement, but when I try =IF(A2="*web*",1,0) with the wild card "*" it does not seem to work. Any ideas? Thank you, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
Macro or Formula to remove Text from Cells | Excel Worksheet Functions | |||
pasting or moving formula cells without updating formulas | Excel Discussion (Misc queries) | |||
How To Use Cells Without Values in a Formula | Excel Worksheet Functions | |||
My formula screws up other reference cells... | Excel Discussion (Misc queries) |