Extracting Comment Text
One last thing you can do to cell comments is extract the text that was placed into a cell comment and have it appear in a cell.
To do this, you need to place a simple custom function into a standard module.
Select Developer → Code → Visual Basic (pre-2007, Tools → Macro → Visual Basic Editor)
or
select Alt/Option-F11, then select Insert → Module and enter the following code:
Function GetCommentText(rCommentCell As Range) Dim strGotIt As String On Error Resume Next strGotIt = WorksheetFunction.Clean _ (rCommentCell.Comment.Text) GetCommentText = strGotIt On Error GoTo 0 End Function
To return to Excel, either click the Close button or press Alt-Q, then save your workbook. Now, in any cell, enter the following formula:
=GetCommentText(A1)
where A1 has a cell comment in it. The comment text will appear in the cell.