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.
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.
Read More: How to Extract Comments from Word Document into Excel Â
Similar Readings
- How to Copy Comments in Excel
- Creating and Editing Comments in Excel
- How to Add Floating Comment in Excel
- How to Add Comment in Excel
- How to Remove Comments in Excel
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.
Read More: How to Filter Cells with Comments in Excel
Similar Readings
- Anchoring Comment Boxes in Excel
- How to Reply to a Comment in Excel
- How to Export Instagram Comments to Excel
- Difference Between Threaded Comments and Notes in Excel
- How to Hide Comments in Excel
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.
Read More: Vlookup to Copy Comments in Excel
Similar Readings
- [Fixed!] Comments in Excel Far Away from Cell
- [Solved!] Comments Are Not Displaying Properly in Excel
- [Fixed!] Excel Comment Only Showing Arrow
- How to Make Flashcards in Excel
- How to Edit Comment in Excel
Download Practice Workbook
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!
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
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 :
with this line:
So the final code will be as below:
Hope, it will solve your problem.
Regards
Aniruddah
Exceldemy