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 StringOn Error Resume NextstrGotIt = WorksheetFunction.Clean _(rCommentCell.Comment.Text)GetCommentText = strGotItOn Error GoTo 0End 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.