Extract Cell Comments/Notes to data
 

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?

Extract Cell Comments/Notes to data
 

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

--
Gary''s Student - gsnu200819


"MSpaven75" wrote:

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?

Extract Cell Comments/Notes to data
 

How do I use it though, I can't remember how to edit MACRO's/VBA (been ages!!!)

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200819


"MSpaven75" wrote:

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?

Extract Cell Comments/Notes to data
 

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
---
"MSpaven75" wrote:
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?

Extract Cell Comments/Notes to data
 

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)

"Max" wrote:

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
---
"MSpaven75" wrote:
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?

Extract Cell Comments/Notes to data
 

Toools-Macros-select a macro, then click on Edit button to the right


On 11 Gru, 15:34, MSpaven75
wrote:
How do I use it though, I can't remember how to edit MACRO's/VBA (been ages!!!)



"Gary''s Student" wrote:
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

--
Gary''s Student - gsnu200819

"MSpaven75" wrote:

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?- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

Extract Cell Comments/Notes to data
 

No problem, here's the steps to install it:

Press Alt+F11 to go to VBE
Click InsertModule
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
---
"MSpaven75" wrote:
Thanks, but, again, I have forgotten how 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)

Extract Cell Comments/Notes to data
 

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.