Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why wont my comments copy from 1 workbook to another?? | Excel Discussion (Misc queries) | |||
Comments | Excel Discussion (Misc queries) | |||
Paste Link - Cell Comments get lost | Excel Worksheet Functions | |||
How do I export comments into another excel column as cell content | Excel Worksheet Functions | |||
I need a macro to find cut and paste data to new cell | Excel Discussion (Misc queries) |