Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
HTC
 
Posts: n/a
Default 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   Report Post  
N Harkawat
 
Posts: n/a
Default

=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   Report Post  
Roger Govier
 
Posts: n/a
Default

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   Report Post  
HTC
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
trying to create an (almost) circular formula between cells and data validated cells with lists KR Excel Worksheet Functions 0 May 12th 05 07:21 PM
Macro or Formula to remove Text from Cells smck Excel Worksheet Functions 6 May 11th 05 03:22 AM
pasting or moving formula cells without updating formulas jake Excel Discussion (Misc queries) 2 April 14th 05 01:02 PM
How To Use Cells Without Values in a Formula Roger H. Excel Worksheet Functions 2 April 6th 05 01:01 AM
My formula screws up other reference cells... Jambruins Excel Discussion (Misc queries) 2 February 28th 05 03:57 PM


All times are GMT +1. The time now is 07:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"