While working in Excel, in some cases you may need to extract all the comments of cells to a sheet.
It is super time consuming if you extract many comments manually as there is no inbuilt option in Excel.
Here I will show to 2 ways to quickly get all comment text into Excel cell.
Using this method you can quickly get all the Excel comments in a sheet for the purpose of organizing , review and analysis.
Extract all Excel comments to a sheet using VBA
Below is the example table (in sheet2) where some comments are present in different cells.
Below is the output table after extracting all the comments of Excel worksheets.
Comment text column represents the comment text and also provides a link to that cell.
You can click comment text and quickly visit a cell with a comment.
- Below is the Excel VBA code for getting all the comments of a workbook.
Follow the below steps to implement the code
- Press Alt+F11 or right-click on any sheet then click the View code option to view VBA IDE panel.
- Create a new standard module in VBA IDE.
- Creating Standard module in Excel VBA IDE
- Copy and paste the below code into the newly created module.
- Now come to Excel and press F8 shortcut key to bring the Macro pane.
- Select “exctractComments” from the Macro pane list then click on the Run button.
Note: Make sure to save the workbook as “.xlsm” (macro) or “.xlsb” (binary) format.
Suggested Readings
Know 6 different ways to run VBA or Macro in Excel
How to insert and run VBA code in Excel?
How to make Microsoft Excel Add in?
Sub exctractComments()
Dim ws As Worksheet
Dim cmnt As Comment
Dim cmntSht As Worksheet
Dim cmntShtNm As String
cmntShtNm = "AllComments"
On Error Resume Next
If Worksheets(cmntShtNm) Is Nothing Then
Set cmntSht = Worksheets.Add
cmntSht.Name = cmntShtNm
With cmntSht
.Range("A1").Value = "Sheet Name"
.Range("B1").Value = "Cell Address"
.Range("C1").Value = "Comment by"
.Range("D1").Value = "Comment text"
.Range("A1:D1").Font.Bold = True
End With
End If
Set cmntSht = Worksheets(cmntShtNm)
On Error GoTo 0
Dim lrw As Long, txt As String, lnkAdd As String
lrw = cmntSht.Range("B" & Rows.Count).End(xlUp).Row + 1
For Each ws In Worksheets
If ws.Comments.Count = 0 Then GoTo jmp
For Each cmnt In ws.Comments
cmntSht.Range("A" & lrw).Value = ws.Name
cmntSht.Range("B" & lrw).Value = cmnt.Parent.Address
cmntSht.Range("C" & lrw).Value = cmnt.Author
txt = Replace(cmnt.Text, cmnt.Author & ":" & Chr(10), "")
If txt = "" Then txt = "-"
lnkAdd = "'" & ws.Name & "'!" & cmnt.Parent.Address
cmntSht.Hyperlinks.Add Anchor:=cmntSht.Range("D" & lrw), _
Address:="#" & lnkAdd, TextToDisplay:=txt
lrw = lrw + 1
Next cmnt
jmp:
Next ws
cmntSht.Activate
End SubExcel formula to get comment text
In the below image, you can see f_exctractComments formula is used to get comment text.
Syntax : f_exctractComments(cel As Range)
By default this formula is not there in Excel, it is build by using the VBA function or VBA UDF (User Defined Function).
To implement the below VBA UDF or custom function follow the below steps.
- Press ALT+F11 to bring VBA IDE.
- Create a new standard module in VBA IDE.
- Copy paste the below code into the newly created module.
Note: It is not necessary to always create a new module, you can also paste the code into an already existing or created standard module.
Function f_exctractComments(cel As Range) As String
Dim cmntStr As String
Dim auth As String
On Error Resume Next
cmntStr = cel.Comment.Text
If Err.Number <> 0 Then
f_exctractComments = ""
Exit Function
End If
auth = cel.Comment.Author
f_exctractComments = Replace(cmntStr, auth & ":" & Chr(10), "")
End Function
