How to Extract Comments in Excel (3 Suitable Examples)

To illustrate how to extract comments in Excel, we’ll use a sample dataset with 4 comments in the range of cells C4:C8.

The VBA module is available in the Developer tab. If you don’t have the tab in your Excel ribbon, you have to enable the Developer tab from the Excel options.

Method 1 – Extracting Comments Using User Defined Function

Steps:

• In the Developer tab, click on Visual Basic. The shortcut key to open the Visual Basic Editor is ‘Alt+F11’.

Note: If the Developer tab is not in your Excel ribbon, you have to enable it from Excel options.

• In the Insert tab on the dialog box, click Module.

• In the editor box, enter the following visual code.

Function Extract_Comments(xCell As Range) As String
On Error Resume Next
Extract_Comments = xCell.Comment.Text
End Function

• Press ‘Ctrl+S’ to save the code.
• Close the Editor
• In cell E5, enter the following formula:

• Press Enter.

• Drag the Fill Handle icon to cell E8.

Read More: How to Extract Comments in Excel

Method 2 – Using Add-ins and Applying VBA Macro to Extract Comments

Steps:

• In the Developer tab, click on Visual Basic.

• In the Insert tab on the dialog box, click Module.

• Enter the following visual code in the editor box.

Sub Extract_Cell_Comments()
Dim Extract_Comment As Comment
Dim x As Integer
Dim work_sheet As Worksheet
Dim C_S As Worksheet
Set C_S = ActiveSheet
If ActiveSheet.Comments.Count = 0 Then Exit Sub

For Each work_sheet In Worksheets
  If work_sheet.Name = "Comments" Then x = 1
Next work_sheet
    
If x = 0 Then
  Set work_sheet = Worksheets.Add(After:=ActiveSheet)
  work_sheet.Name = "Comments"
Else: Set work_sheet = Worksheets("Comments")
End If

For Each Extract_Comment In C_S.Comments
  work_sheet.Range("B4").Value = "Reference"
  work_sheet.Range("C4").Value = "Author"
  work_sheet.Range("D4").Value = "Comment"
  With work_sheet.Range("B4:D4")
    .Font.Bold = True
    .Font.Color = RGB(255, 255, 255)
    .Interior.Color = RGB(32, 55, 100)
    .Rows.RowHeight = 20
  End With
  If work_sheet.Range("B5") = "" Then
    work_sheet.Range("B5").Value = Extract_Comment.Parent.Address
    work_sheet.Range("C5").Value = Left(Extract_Comment.Text, InStr _
    (1, Extract_Comment.Text, ":") - 1)
    work_sheet.Range("D5").Value = Right(Extract_Comment.Text, Len(Extract_Comment.Text) _
    - InStr(1, Extract_Comment.Text, ":"))
  Else
    work_sheet.Range("B4").End(xlDown).Offset(1, 0) = Extract_Comment.Parent.Address
    work_sheet.Range("C4").End(xlDown).Offset(1, 0) = Left(Extract_Comment.Text, _
    InStr(1, Extract_Comment.Text, ":") - 1)
    work_sheet.Range("D4").End(xlDown).Offset(1, 0) = Right(Extract_Comment.Text, _
    Len(Extract_Comment.Text) - InStr(1, Extract_Comment.Text, ":"))
  End If
Next Extract_Comment
End Sub

• Press ‘Ctrl+S’ to save the code in that module.
• Close the Editor
• Click on File > Save As.
• Rename for your file and choose the file format as .xlam. For this file, we will set the name as Extract Comments.
• Save the file in your desired location. In this case, we will save it on the Desktop.

• Go to the Developer tab and select the Excel Add-ins option from the Add-ins group.

• You may not find the name of the add-ins as we didn’t save it at the built-in location. To get it, click on the Browse option.

• Go to the location where you have saved the file.
• Select the file and click OK.

• Check the Extract Comments and click OK. The add-ins will be added.

• In the Developer tab, click on Macros.

• Select Extract_Cell_Comments.

• Click on the Run button to run this code.
• A new sheet named Comments will be created in the Sheet Bar and in that sheet, the comments are extracted. Format the cell range according to your needs.

Method 3 – Extracting Comments to New Worksheet

Steps:

• Go to the Developer tab and click on Visual Basic.

• In the Insert tab on the dialog box, click on the Module.

• Enter following VBA code.

Sub Extract_All_Comments()

Dim wrk_sheet As Worksheet
Dim comm_list As Comment
Dim cal As Long

cal = 0

For Each wrk_sheet In ActiveWorkbook.Worksheets
    For Each comm_list In wrk_sheet.Comments
        Worksheets("Result").Range("B5").Offset(cal, 0).Parent.Hyperlinks.Add _
            Anchor:=Worksheets("Result").Range("B5").Offset(cal, 0), _
            Address:="", _
            SubAddress:="'" & wrk_sheet.Name & "'!" & comm_list.Parent.Address, _
            TextToDisplay:="'" & wrk_sheet.Name & "'!" & comm_list.Parent.Address
        Worksheets("Result").Range("C5").Offset(cal, 0).Value = comm_list.Author
        Worksheets("Result").Range("D5").Offset(cal, 0).Value = comm_list.Text
        cal = cal + 1
    Next comm_list
Next wrk_sheet

End Sub

• Press ‘Ctrl+S’ to save the code.
• Close the Editor
• Click on the Plus sign in the Sheet Bar and rename it as Result.

• Go to the Developer tab and click on Macros.

• Select Extract_All_Comments.
• Click on the Run button to run this code.

• The comments will be extracted in the sheet named Result. Format the cells if you require any specific cell formatting.

Read More: Convert Comments to Notes in Excel 

Download Practice Workbook

Related Articles

• How to Populate Comment from Another Cell in Excel
• How to Extract Comments from Word Document into Excel
• How to Export Instagram Comments to Excel
• [Fixed!] Comments in Excel Far Away from Cell
• [Fixed!] Excel Comment Only Showing Arrow
• [Solved!] Comments Are Not Displaying Properly in Excel
• Difference Between Threaded Comments and Notes in Excel

<< Go Back to Comments in Excel | Learn Excel