MS-Excel / General Formatting

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.

[Previous] [Contents]

In this tutorial:

  1. Customize Cell Comments
  2. Adding a Picture
  3. Extracting Comment Text