Extract Cell Comments/Notes to data

  • Thread starter Thread starter MSpaven75
  • Start date Start date
M

MSpaven75

I have a spreadsheet with Yes/No's a field values and all the meaningful data
as cell comments, I know there is a way to get these notes as cell contents
but can't remember how.

Ideas anyone?
 
Lots of different ways.

Say we have comments in cells and want to put the data in the adjacent cell
to the right.

So if B9 had a comment, we want that text in C9.

Try this tiny macro:

Sub DisplayComments()
Dim c As Comment
If ActiveSheet.Comments.Count = 0 Then Exit Sub
For Each c In ActiveSheet.Comments
Range(c.Parent.Address).Offset(0, 1).Value = c.Text
Next
End Sub
 
I use this UDF (from David M, IIRC)

Function MyComment(rng As Range)
Application.Volatile
Dim str As String
str = Trim(rng.Comment.Text)
'// If you want to remove Chr(10) character from string, then
str = Application.Substitute(str, vbLf, " ")
MyComment = str
End Function


In Excel,
use it in say, B1: =mycomment(A1)
to return A1's "Comment" contents
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
 
Thanks, but, again, I have forgotten hopw to create UDF's etc. (it was a UDF
I have used before)

Can someone talk me through as if I were daft? (which I am not, honest)
 
No problem, here's the steps to install it:

Press Alt+F11 to go to VBE
Click Insert>Module
Copy n paste the UDF (or Sub) into the code window (whitespace on the right)
Press Alt+Q to get back to Excel

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
 
Thanks to those who helped, this is now working.

I'll make a two pint donation to the "I like beer, buy me beer" fund over
the weekend.
 
Back
Top