EXCEL FILTER OF COMMENT CONTENT

[Bart]

I want to filter on a key word for a group of cells and the contents of the
comments. Cannot find any information on filtering on the key word within the
comment.
Thanks
Bart

 

[JLatham]

That's probably because you can't filter by words within comments with any
built-in Excel functions?

You'd need a custom macro to go through the cells in question and examine
them to see if they have a comment and then 'filter' based on the results of
the examination of the comment text after determining that a cell even had a
comment associated with it.

 

[Dave Peterson]

Maybe just using Edit|Find and searching through comments would be sufficient???

 

[smartgal]

On the filter, you can choose the "custom" option and choose "contains."
From there, just enter the criteria you're seeking.

Keep in mind that if you're looking for something like "NA" it will find
*all* instances where "NA" appears so if the word "finance" appears, it will
show up in your result so you need to be as specific as you can. There are a
lot of options in "custom" so after some experimentation, you'll probably
come to something useful.

Good luck!

 

[Peo Sjoblom]

You can't filter comment

--


Regards,


Peo Sjoblom

 

[smartgal]

Whoops, just thought it was comments in a field. My bad!

 

[Dave Peterson]

Maybe you could use a little VBA user defined function to retrieve the comment
into an adjacent cell:

Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm