Hi,

I have 1000 comments in column A, one comment cell. In column B against each
comment I want to do a formula that returns a value (say 'R') if the word
'Rostering' appears in it.

Could someone please provide me with the correct 'IF' statement or any
alternatives?

Thanks
--
..

One way:

=IF(ISNUMBER(SEARCH("rostering",A1)),"R","")

Copy down as needed.

Biff

"LucB" wrote in message
...
Hi,

I have 1000 comments in column A, one comment cell. In column B against
each
comment I want to do a formula that returns a value (say 'R') if the word
'Rostering' appears in it.

Could someone please provide me with the correct 'IF' statement or any
alternatives?

Thanks
--
.

On Tue, 28 Nov 2006 17:14:00 -0800, LucB
wrote:

Hi,

I have 1000 comments in column A, one comment cell. In column B against each
comment I want to do a formula that returns a value (say 'R') if the word
'Rostering' appears in it.

Could someone please provide me with the correct 'IF' statement or any
alternatives?

Thanks

I don't believe there are any functions that return the contents of cell
comments. So you'll need to write a VBA routine, and then test the returned
string.

So a formula of the type:

=IF(ISERR(FIND("Rostering",getcomment(A1))),"","R" )

Note that FIND is case-sensitive. If you want it to be case-insensitive, use
SEARCH.

=IF(ISERR(SEARCH("Rostering",getcomment(A1))),""," R")

The test text can be in a cell for easier editing, e.g:

=IF(ISERR(FIND(E1,getcomment(A1))),"","R")

To return the comment, you will need to write a short VBA routine.

<alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then select

Insert/Module and paste the code below into the window that opens.

You can now use the GETCOMMENT function in your worksheet as in the above
formula.

'==========================================
Option Explicit

Function GetComment(rg As Range) As String
GetComment = rg.Comment.Text
End Function
'==================================



--ron