David H. asks,“Using the ‘Find’ feature in Excel works when you need to locate items quickly, but is there a way to use this, or another function, that will expand the search to include text in any comments within the worksheet?”
Cell comments are a great way to document a spreadsheet. But if you later need to search all cell comments that include particular text, it is fairly difficult.
One option is to use the Show All Comments icon on the Review tab in Excel. This will display all of the comments. If you have a small data set, then it is easy to read through the comments, looking for certain text. However, this solution is not perfect. If the text of the comment extends beyond the size of the comment, you will not notice text that extends beyond the comment.
Fig. 1
The next option is a macro to search through comments. Follow these steps to add a macro to the workbook:
- Type Alt+T followed by M, S to check your macro security. If the setting is currently “Disable all macros without notification”, change it to “Disable all macros with notification”.
- Use File, Save As to save your file as either an XLSM or XLSB or XLS file type. The default XLSX file precludes macros.
- Use Alt+F11 to switch to VBA
- Select Insert, Module to insert a new module into your workbook
- Copy and paste the following into the blank module.
Sub SearchCommentsOnCurrentWorksheet()
Dim c As Comment
Dim SearchFor As Variant
Dim TxtToSearch As Variant
Dim Ctr As Integer
Dim Msg As Variant
SearchFor = LCase(InputBox(Prompt:=”Text to find in comments?”))
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
Ctr = 0
For Each c In ActiveSheet.Comments
TxtToSearch = LCase(c.Text)
If InStr(1, TxtToSearch, SearchFor) > 0 Then
c.Visible = True
c.Parent.Select
Ctr = Ctr + 1
If Ctr = 1 Then
Msg = ” comments found:” & vbLf & c.Parent.Address
Else
Msg = Msg & vbLf & c.Parent.Address
End If
End If
Next c
If Ctr = 0 Then
MsgBox x & ” not found in current worksheet comments.”
Else
MsgBox Ctr & Msg
End If
End Sub
Then continue with these next three steps:
- Pre Alt+Q to close VBA and return to Excel.
- Press Alt+F8 to display a list of macros.
- Select SearchCommentsOnCurrentWorksheet and press Run
The macro will ask you for the text that you want to find.
Fig. 2
Enter some text. Click OK. The macro will find all comments that contain the text. The comment will be shown for those cells and a list of the cells will appear in the dialog box, as shown in Figure 3.
Fig. 3
To subsequently hide the comments, click the Show All Comments icon in the Review tab of the ribbon. Click this icon twice. The first click will show all comments. The second click will hide all comments.
You can also use this macro to find all comments created by one particular person. Here are all of the Joe Smith comments (see Figure 4).
Fig. 4
Caution: if you regularly keep some comments displayed to provide instructions, you should not use this macro, as it will hide all comments.
If you find that this macro is useful and you will want to have it always available, you can move the code to your personal macro workbook and assign the macro to an icon on the Quick Access Toolbar.
CFO Contributor and Microsoft MVP Bill Jelen is the author of 34 books about Microsoft Excel, including Learn Excel 2007-2010 from MrExcel – 512 Excel Mysteries Solved. Bill regularly selects reader questions from the Community Center (to the right of this article) to answer in his columns.