Share via

Find cells with comments in Excel

Anonymous
2021-09-29T21:56:08+00:00

Is there a function to return whether or not a cell has a comment?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-09-29T22:39:15+00:00

    There is no built-in function for this. You could copy the following user-defined function into a module in the Visual Basic Editor:

    Function HasComment(rng As Range) As Boolean
    If rng.CountLarge = 1 Then
    HasComment = Not (rng.Comment Is Nothing)
    End If
    End Function

    Use like this:

    =HasComment(A1)

    You can combine this with other functions in a formula, of course.

    You'll have to save the workbook as a macro-enabled workbook (*.xlsm) and allow macros when you open it.

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-09-29T22:27:35+00:00

    HansV

    I have learned something already which I am sure I will be able to use, but it is not quite what I was looking for.

    I have a spreadsheet with lots of specialized data in it. I need to extract a number from my data that says how many times certain conditions have been met. I have managed to filter out prospective matches down to the last parameter, which happens to be "does this cell have a comment?" I am looking for a function in Excel that will return whether or not a particular cell has a comment, then count how many of those cells there are and then subtract that number from the count of cells that satisfied all the previous parameters.

    Is there a function in Excel that can return a TRUE/FALSE or 1/0 value for that attribute of a cell?

    Thank you for your timely answer!

    1 person found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-09-29T21:59:26+00:00

    You can find all cells with comments as follows:

    Press F5 or Ctrl+G to activate the Go To dialog.

    Click Special...

    The Comments radio button will be selected by default, so you only need to click OK.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2021-09-29T23:05:02+00:00

    That is exactly what I needed. Thank you!

    0 comments