get excel comments contents in to cells

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 Sub

Excel 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

Download file – Extract comment in excel