Search by the word in comment


Can we search by the word in comment of a cell in excel 2016?



Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1247) Sep 30, '17 at 5:30 am
Add to Discussion



Yes, you can. However, to the best of my knowledge, you would require VBA to do so.


Use a macro like this:

Sub CommentsList()

Dim WS As Worksheet
Dim Rng As Range
Dim cell As Variant
Dim i As Single

Set Rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
Set WS = Sheets.Add

WS.Range("A1") = "Cell"
WS.Range("B1") = "Comment"

i = 2

For Each cell In Rng
    WS.Range("A" & i) = cell.Address
    WS.Range("B" & i) = cell.Comment.Text
    i = i + 1
Next cell

End Sub

It will list the comments from the current worksheet.

Adjust the cell references in the macro to the columns where you want the results to appear. By default, they appear in column A and B and you can change those values in the macro to whatever column you want.

Once you have the list of comments in Excel, you can use Ctrl + F to find the text.


Answer the Question

You must create an account to use the forum. Create an Account or Login