Extract both Cell and Comment

[kanonathena]

Hi there

I'm trying to extract both value and comment of a cell to another cell.

For example, I can put "=A1" in B1 to get A1's value in B1, but A1 also has a comment, how do I get this comment as well? Note that I actually use formulas to extract A1 to B1 but I also want to extract A1's comment, so basically make B1 a carbon copy of A1.

I looked around on internet, there is VBA code to extract the comment's value to a cell, that's not what I want.

Thanks for your help.

[HaHoBe]

Hi, kanonathena,

sorry I´m not aware of just a plain formula with standard functions being able to do so. Using a UserDefined Function may help with that but that function is VBA which you want to avoid.

UDF goes into a standard module

Please Login or Register  to view this content.

Used in a cell:

Please Login or Register  to view this content.

Ciao,
Holger

[kanonathena]

Hi Holger,

Thanks for the code. I think I wasn't clear. I don't want to extract the comment to a separate cell, I want to leave the comment in the comment format (in that little yellow box). So A1 has a yellow box I want to make B1 = A1 plus the little yellow box.

What I am doing right now is to make a report based on a data sheet. People put comment (in the yellow box) in cells in the data sheet. When the report sheet grab a cell in the data sheet I also want it to grab the comment.

I don't know if it's possible because you need to in one cell enter both the formula to grab the cell value and also something to grab the comment. Maybe use a macro instead of a user defined formula?

Let's say if have text string in A1 "This is Sue's salary", can you turn it into a comment in B1 with something?

[HaHoBe]

Hi, kanonathena,

AFAIK only with VBA or a function used in VBA but not as an UDF being applied in a cell.

Ciao,
Holger