Extract Comments and paste as values
 

Hello all,

Hopefully there is a quick and easy answer to this question, but here
it goes. Some cells in our Excel worksheet have comments. What we
would like to do is take the "text" that is in the comment and extract
it to its own cell.

For example, if Cell B1 has "verify address" as its comment, we would
like the value of cell C1 to be _verify_address_. Simply put, we want
to take the information that is in the comment and place that in its own
cell.

Is there an easy way to do this?

Thank you in advance!!

~Brock


--
Btibert
------------------------------------------------------------------------
Btibert's Profile: http://www.excelforum.com/member.php...fo&userid=8559
View this thread: http://www.excelforum.com/showthread...hreadid=472175

Brock

Macro to do this.

Sub Show_Comments()
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange
If Not Cell.Comment Is Nothing Then
Cell.Offset(0, 1).Value = Cell.Comment.Text
End If
Next Cell
End Sub

You must be sure that the cell to the right of any comment is empty or it will
be overwritten.

May be better to list all the comments and cell addresses on another sheet.

From Debra Dalgleish............

Sub ListComms()
Dim Cell As Range
Dim sh As Worksheet
Dim csh As Worksheet
Set csh = ActiveWorkbook.Worksheets.Add
csh.Name = "Comments"
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < csh.Name Then
For Each Cell In sh.UsedRange
If Not Cell.Comment Is Nothing Then
With csh.Range("a65536").End(xlUp).Offset(1, 0)
.Value = sh.Name & " " & Cell.Address
.Offset(0, 1).Value = Cell.Comment.text
End With
End If
Next Cell
End If
Next sh
End Sub


Gord Dibben Excel MVP

On Fri, 30 Sep 2005 12:53:43 -0500, Btibert
wrote:


Hello all,

Hopefully there is a quick and easy answer to this question, but here
it goes. Some cells in our Excel worksheet have comments. What we
would like to do is take the "text" that is in the comment and extract
it to its own cell.

For example, if Cell B1 has "verify address" as its comment, we would
like the value of cell C1 to be _verify_address_. Simply put, we want
to take the information that is in the comment and place that in its own
cell.

Is there an easy way to do this?

Thank you in advance!!

~Brock