SubscribeGet cell comments using Excel Formula
Excel has a very useful feature called “cell comments” using which you can add comment to a cell. This is a very good way to gather remarks and review comments when a workbook is shared with colleagues and others. But what if you have typed a ton of cell comments and now want a way to extract them and do something with that data?
Well, no need to select each comment and copy the contents. You can use a simple user defined formula (UDF) to do just that. Here is a one line formula that I wrote.
Function getComment(incell) As String
' aceepts a cell as input and returns its comments (if any) back as a string
On Error Resume Next
getComment = incell.Comment.Text
End FunctionTo make it work,
To make the getComment() UDF work for you, you need to install it first. Here is a step by step guide if you dont know how to install user defined formulas in excel.
The simple and easy version:
- Copy the above vba code
- Open a new excel file
- Right click on any sheet tab name and select “view code” option.
- Don’t be scared of the VBA editor. Take a deep breath.
- Click on “Insert” menu option and select “Module”
- Paste the code in the new module
- Save the workbook and start using the getComment() formula.
The lengthy and more stable version:
- Same as first 6 steps above.
- Press ctrl+s to save the work book. Specify “excel add-in” as the file type.
- Now, go back to excel and install this new add-in
- That is all. Now you can use getComment() formula in your workbooks.
Related: How to change the shape of cell comments from rectangle to any other shape
Trackbacks & Pingbacks
- Pingback by Format Excel Comment Boxes using VBA Macros | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on September 11, 2009 @ 9:44 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
it is greet UDF
but the result of this new function contain the username before the comment
so to get the comment only i used that formula
=RIGHT(getcomment(A1),LEN(getcomment(A1))-FIND(”:”,getcomment(A1)))
thanks at all
@Moatasem.. agree.. you can also use the author attribute of the comment object to find who added the comment.
I would probably typecast the incell variable, to make sure someone doesn’t try to pass a non-Range object to it. I wonder what would happen if a multi-cell Range was passed, with multiple comments, what would happen?
Also, if you delete the comment author from the comment text, you won’t be able to pull the comment author from there. But I do notice that, even if you do that, the comment author is still displayed in the status bar. I wonder if it’s possible to access that text somehow, since it doesn’t change even if you edit the comment.
@JP .. wonderful suggestions. I thought of typecasting and non-range exceptions. Initially the UDF had only one line. I later added the on error resume next to take care of pretty much everything. Comment author is a problem though, but I guess we can easily parse it out as Moatasem suggested above…
Hi. I’m still learning about UDFs, so I thought I’d try this as a good training exercise for myself. I’ve set it up and it seems to work. But, I’ve noticed that if I change the comment text, the udf doesn’t automatically update. I’ve got calculation set to automatic, and I’ve also tried hitting f9 to get it to recalc. Any ideas how to force it to update after editing comments ?
@Gerald: that is tricky. I just realized it myself. The only way to recalculate this formula is to go to the cell, hit f2 and then hit enter. I am not sure why this is happening though.
Any UDF / Macro experts out there… any idea why this might be happening ?
Add the following line to the UDF, right after ‘On Error Resume Next’:
Application.Volatile True
Then press F9 to recalc as needed.
Well that kind of works – thanks JP. If I hit F9, it does now re-calculate. But if I don’t hit F9, it doesn’t re-calc, if calculation is set to automatic. What it seems to do is, IF there is a formula on the worksheet, then it WILL update the comment, I’m guessing because the formula is somehow forcing a general calculation. But if there are no formulas elsewhere in the sheet, then it only updates on F9. As it is, this is probably OK for most users. But just out of curiosity, is there a way to force updating on a worksheet without other formulas ? By the way, I’m using 2003.
I’m using 2003 as well, and my experience is slightly different.
Because there are no events that fire when a comment is updated, it doesn’t matter if the worksheet has formulas (I checked the Change and Calculate Events for Workbook and Worksheet). Just editing a comment won’t force recalculation, even if the worksheet has formulas. After editing a comment, you would still have to press F9.
What does work, however, is if you edit a *formula* on the same worksheet. That causes recalculation, which in turn causes the UDF to update.
JP – sorry, to be precise, I think my experience is the same as your’s. With the modified code, simply modifying the comment does not result in an update to the result of getcomment(), whether or not there are formulas on the sheet. Hitting f9 does seem to force an update to getcomment(). Also, as you say, editing a formula also updates getcomment(). Not only that, but making other changes (such as entering a text string in a previously blank cell) also forces an update to getcomment(). Thanks for your help !
Makes sense. Updating a cell (not the comment, unfortunately) would trigger recalculation.