Comments in Excel can be stored in a cell and their locations are identified by a small red triangle in the top right of a cell. These comments can contain lots of text, carriage returns etc and can be very useful for a user in their own spreadsheet or a user wishing to explain specific parts of a spreadsheet to another user. Whilst the comments can be very useful, they can also become quite cumbersome and when there are too many comments in a worksheet it becomes difficult to see them and when the mouse moves over the cell, the comments can block out parts of the spreadsheet from view.
In these instances, or for times when you want to know what all the comments are, a UDF can be pasted into a VBA module which instantly opens up a new formula function to use in the worksheet or apply in a VBA script to extract all the comments to see in a cell.
Once this UDF is pasted into a module, it becomes a simple formula in a worksheet.
This will return the comment text from cell A1 in the cell where you have placed the formula. A VBA script could be used to analyse an entire workbook and return the comments in a list in a fresh workbook. Feel free to contact us using the form below for assistance, or leave a comment and we’ll get back to you.
Function GetCommentText(rCommentCell As Range)
Dim cmnt As String
On Error Resume Next
cmnt = WorksheetFunction.Clean _
GetCommentText = scmnt
On Error GoTo 0