How to Extract Comments in Excel (3 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we add some comments in our Excel worksheet for others to make the spreadsheet more convenient. As a result, sometimes it becomes required for us to get them in one sheet. This article will show you how to extract comments from cells in Excel. If you are interested to know about it, download our workbook and follow us.

Table of Contents hide

How to Extract Comments in Excel: 3 Suitable Examples

1. Extracting Comments Using User Defined Function

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

3. Extracting Comments to New Worksheet

Conclusion

How to Extract Comments in Excel: 3 Suitable Examples

To demonstrate the process, we consider a dataset of expense reports of a person for several days. The dates are in column B, the purpose of the expense is in column C, and the expense amounts are in column D. Four comments are available in the range of cells C4:C8. We are going to extract them.

The VBA code writing interface 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.

1. Extracting Comments Using User Defined Function

We are going to create a customized function by using VBA to extract the comments from the cells. The steps of this process are shown below.

📌 Steps:

First of all, in the Developer tab, click on Visual Basic. Besides that, you can also press ‘Alt+F11’ to open the Visual Basic Editor.

A dialog box will appear.
Now, in the Insert tab on that box, click Module.

A white interface will appear in front of you.
Then, write down the following visual code in that empty editor box.

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

After that, press ‘Ctrl+S’ to save the code.
Close the Editor tab.
Now, in cell E5, write down the following formula:

=Extract_Comments(C5)

Press Enter.

Then, drag the Fill Handle icon to copy the formula up to cell E8.
You will get all the comments as sting into the cells.

At last, we can say that our customized function worked successfully and we are able to extract comments in Excel.

Similar Readings

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

In this following approach, we will create customized Excel Add-ins to extract comments from Excel cells. The procedure explains below step by step:

📌 Steps:

At first, in the Developer tab, click on Visual Basic. Or You can simply press ‘Alt+F11’ to open the Visual Basic Editor.

A dialog box will appear.
After that, in the Insert tab on that box, click Module.

As a result, a white interface will appear in front of you.
Now, write down the following visual code in that empty 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

Next, press ‘Ctrl+S’ to save the code in that module.
Close the Editor tab.
After that, click on File > Save As.
The Save As window will appear on your device.
Write down a suitable name for your file and choose the file format as .xlam. For our file, we set the name as Extract Comments.
Then, save the file in your desired location, In our case, we save it on the Desktop.

Now, go to the Developer tab and select the Excel Add-ins option from the Add-ins group.

A small dialog box titled Add-ins will appear.
You may not find the name of our add-ins as we didn’t save it at the built-in location. To get it, click on the Browse option.

A window called Browse will appear. Go to the location where you save the file last time.
Then, select the file and click on OK.

After that, check the Extract Comments and click OK. The add-ins will be added.

Again, in the Developer tab, click on Macros.

A new dialog box called Macro will appear. Select Extract_Cell_Comments.

Then, click on the Run button to run this code.
You will see 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.

Finally, we can say that our visual code worked successfully, and we are able to extract comments in Excel.

Similar Readings

3. Extracting Comments to New Worksheet

In the method, we will write a visual basic code and create a new sheet to extract the comments. The process is similar to any other VBA coding, though we are sharing the steps for your convenience.

📌 Steps:

To start the approach, go to the Developer tab and click on Visual Basic. Besides it, you can also press ‘Alt+F11’ to open the Visual Basic Editor.

A dialog box will appear.
Then, in the Insert tab on that box, click on the Module.

As a result, a window called Browse will appear
After that, write down the following visual code in that empty editor box.

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

Next, press ‘Ctrl+S’ to save the code.
Close the Editor tab.
Now, click on the Plus sign in the Sheet Bar and rename it as Result.

Again, go to the Developer tab and click on Macros.

A new dialog box called Macro will appear. Select Extract_All_Comments.
At last, click on the Run button to run this code.

You will get the comments extracted in the sheet entitled Result. Format the cells if you required any specific cell formatting.

So, we can say that our visual code worked successfully and we are able to scroll one row at a time in Excel.

Similar Readings

Download Practice Workbook

Extract Comments.xlsm

Extract Comments.xlam

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to extract comments in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations. Keep learning new methods and keep growing!

2 Comments

Dave Jul 24, 2023 at 10:28 PM

Hello
Thank you for the vba code for extracting comments to a new sheet.
How would I change the code to have the cell value be displayed instead of the
author.
instead of ‘outputting” Asus for all comments extracted it would say
“NY to Philadelphia”
thanks for any feedback
- Reply
  Aniruddah Alam Jul 25, 2023 at 11:30 AM
  Thank you Dave for your query. In order to replace the Author with Cell value, in the Extract_All_Comments subroutine, you can replace the following line :
```
Worksheets("Result").Range("C5").Offset(cal, 0).Value = comm_list.Author
```
  with this line:
```
Worksheets("Result").Range("C5").Offset(cal, 0).Value = wrk_sheet.Range(comm_list.Parent.Address).Value 
```
  So the final code will be as below:
```
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 = wrk_sheet.Range(comm_list.Parent.Address).Value
        Worksheets("Result").Range("D5").Offset(cal, 0).Value = comm_list.Text
        cal = cal + 1
    Next comm_list
Next wrk_sheet
End Sub
```
  Hope, it will solve your problem.
  Regards
  Aniruddah
  Exceldemy

How to Extract Comments in Excel (3 Suitable Examples)

How to Extract Comments in Excel: 3 Suitable Examples

1. Extracting Comments Using User Defined Function

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

3. Extracting Comments to New Worksheet

Conclusion

Related Articles

Leave a reply Cancel reply