Finding and Replacing Text in Notes and Comments

Gerry has a workbook containing 22 worksheets. Each worksheet has about 20 comments. Some of the comments make reference to a company division. He would like to do a mass search and replace of the comments to find each reference (for example, "ABC Division") and replace it with something else (for example, "XYZ subsidiary").

There is no way to do this without using a macro. The regular Find and Replace capabilities in Excel don't allow you to find text within notes or comments, but you can use macro commands. The following is a simple macro to do the replacing:

Sub ReplaceNotesComments()
    Dim c1 As Comment
    Dim c2 As CommentThreaded  ' Remove if Excel 2016 or earlier
    Dim w As Worksheet
    Dim sFind As String
    Dim sReplace As String
    Dim sCmt As String

    sFind = "ABC Division"
    sReplace = "XYZ subidiary"

    For Each w In ActiveWorkbook.Worksheets
        ' Replace in any notes
        For Each c1 In w.Comments
            sCmt = c1.Text
            If InStr(sCmt, sFind) <> 0 Then
                sCmt = Application.WorksheetFunction. _
                  Substitute(sCmt, sFind, sReplace)
                c1.Text Text:=sCmt
            End If
        Next

        ' Replace in any threaded comments
        ' Running on Excel 2016 and earlier will generate
        ' an error, so remove this code if on earlier version
        For Each c2 In w.CommentsThreaded
            sCmt = c2.Text
            If InStr(sCmt, sFind) <> 0 Then
                sCmt = Application.WorksheetFunction. _
                  Substitute(sCmt, sFind, sReplace)
                c2.Text Text:=sCmt
            End If
        Next
    Next
End Sub

The key lines here are those that set the sFind and sReplace variables. You should set those to reflect what you are searching for and what you want it replaced with, respectively.

Note, as well, that the macro has separate loops that step through first notes and then through comments. (Notes were called comments in Excel 2016 and earlier. Starting in Excel 2019, the old-fashioned comments are now called notes and comments now refer to threaded comments.) If you will be running this macro on older versions of Excel, 2016 and earlier, then you'll want to modify the macro by deleting the code indicated. You cannot just test for versions because, well, there is no straightforward way to test for versions these days and the Dim statement for variable c2 will generate an error right off the bat.

The macro works by stepping, first, through each comment (note) in each worksheet and making the changes. It then steps through each comment (threaded) and makes changes there, as well.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11149) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.