Help I need to know how to write a simple formula, not having any
 

Hello anyone,
I am trying to write a simple formula that will do the following...
I have text in columns A1:A100. I want to search for the word "wind" on each
line of text A1:A100. Needless to say it takes a long time to search each
line manually.
Then when the word "wind" has been found...I want to assign it a number
(like 6) in column B1:B100, whenever the word "wind" is found within the text
in column A. I would also like to find other key words like "about"
"portable" and "how to" (not case sensitive)...and also assign a number for
each. If none of my words comes up in a line of text, then it should not
put anything assigned to it.
--
Example of what I want to see
A B
wind power in homes 6
solar cells
about solar power 1
solar portable power 5
home solar
build wind system 6

It seems like it would be fairly easy to write a formula, but I guess I
just am not that adept at using excel formulas yet.
Any Help would be greatly appreciated. Thanks!!!!!!!!!!


thanks, cb

Help I need to know how to write a simple formula, not having any
 

cbexcel

EditFind: Find What :"Wind"
Replace With: "6"
("No Quotes")
Search by Columns

Does This Help?

Mike Rogers

Do the same for all you want to replace. "cbexcel" wrote:

Hello anyone,
I am trying to write a simple formula that will do the following...
I have text in columns A1:A100. I want to search for the word "wind" on each
line of text A1:A100. Needless to say it takes a long time to search each
line manually.
Then when the word "wind" has been found...I want to assign it a number
(like 6) in column B1:B100, whenever the word "wind" is found within the text
in column A. I would also like to find other key words like "about"
"portable" and "how to" (not case sensitive)...and also assign a number for
each. If none of my words comes up in a line of text, then it should not
put anything assigned to it.
--
Example of what I want to see
A B
wind power in homes 6
solar cells
about solar power 1
solar portable power 5
home solar
build wind system 6

It seems like it would be fairly easy to write a formula, but I guess I
just am not that adept at using excel formulas yet.
Any Help would be greatly appreciated. Thanks!!!!!!!!!!


thanks, cb

Help I need to know how to write a simple formula, not having any
 

try in B2
=if(iserror(search("wind",A2)),0,6)+if(iserror(sea rch("about",A2)),0,2)+...
copy down as wanted.
"cbexcel" wrote:

Hello anyone,
I am trying to write a simple formula that will do the following...
I have text in columns A1:A100. I want to search for the word "wind" on each
line of text A1:A100. Needless to say it takes a long time to search each
line manually.
Then when the word "wind" has been found...I want to assign it a number
(like 6) in column B1:B100, whenever the word "wind" is found within the text
in column A. I would also like to find other key words like "about"
"portable" and "how to" (not case sensitive)...and also assign a number for
each. If none of my words comes up in a line of text, then it should not
put anything assigned to it.
--
Example of what I want to see
A B
wind power in homes 6
solar cells
about solar power 1
solar portable power 5
home solar
build wind system 6

It seems like it would be fairly easy to write a formula, but I guess I
just am not that adept at using excel formulas yet.
Any Help would be greatly appreciated. Thanks!!!!!!!!!!


thanks, cb

thanks bj, it works great!
 

Hello and thanks very much for your help. it really works great!

--
thanks, cb


"bj" wrote:

try in B2
=if(iserror(search("wind",A2)),0,6)+if(iserror(sea rch("about",A2)),0,2)+...
copy down as wanted.
"cbexcel" wrote:

Hello anyone,
I am trying to write a simple formula that will do the following...
I have text in columns A1:A100. I want to search for the word "wind" on each
line of text A1:A100. Needless to say it takes a long time to search each
line manually.
Then when the word "wind" has been found...I want to assign it a number
(like 6) in column B1:B100, whenever the word "wind" is found within the text
in column A. I would also like to find other key words like "about"
"portable" and "how to" (not case sensitive)...and also assign a number for
each. If none of my words comes up in a line of text, then it should not
put anything assigned to it.
--
Example of what I want to see
A B
wind power in homes 6
solar cells
about solar power 1
solar portable power 5
home solar
build wind system 6

It seems like it would be fairly easy to write a formula, but I guess I
just am not that adept at using excel formulas yet.
Any Help would be greatly appreciated. Thanks!!!!!!!!!!


thanks, cb

Help I need to know how to write a simple formula, not having any
 

Hi!

Create a 2 column table with the words and their corresponding number value:

................G...............H
1.........about.............1
2.........portable.........5
3.........wind..............6

Enter this formula in B1 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(OR(ISNUMBER(SEARCH(G$1:G$3,A1))),INDEX(H$1:H$3 ,MATCH(TRUE,ISNUMBER(SEARCH(G$1:G$3,A1)),0)),"")

Copy down as needed.

Biff

"cbexcel" wrote in message
...
Hello anyone,
I am trying to write a simple formula that will do the following...
I have text in columns A1:A100. I want to search for the word "wind" on
each
line of text A1:A100. Needless to say it takes a long time to search each
line manually.
Then when the word "wind" has been found...I want to assign it a number
(like 6) in column B1:B100, whenever the word "wind" is found within the
text
in column A. I would also like to find other key words like "about"
"portable" and "how to" (not case sensitive)...and also assign a number
for
each. If none of my words comes up in a line of text, then it should not
put anything assigned to it.
--
Example of what I want to see
A B
wind power in homes 6
solar cells
about solar power 1
solar portable power 5
home solar
build wind system 6

It seems like it would be fairly easy to write a formula, but I guess I
just am not that adept at using excel formulas yet.
Any Help would be greatly appreciated. Thanks!!!!!!!!!!


thanks, cb