Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Get cell comments using Excel Formula

Posted on September 3rd, 2009 in Learn Excel , VBA Macros - 47 comments

Get Cell Comments using FormulasExcel 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 Function

To 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:

  1. Copy the above vba code
  2. Open a new excel file
  3. Right click on any sheet tab name and select “view code” option.
  4. Don’t be scared of the VBA editor. Take a deep breath.
  5. Click on “Insert” menu option and select “Module”
  6. Paste the code in the new module
  7. Save the workbook and start using the getComment() formula.

The lengthy and more stable version:

  1. Same as first 6 steps above.
  2. Press ctrl+s to save the work book. Specify “excel add-in” as the file type.
  3. Now, go back to excel and install this new add-in
  4. 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

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

47 Responses to “Get cell comments using Excel Formula”

  1. moatasem says:

    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

  2. Chandoo says:

    @Moatasem.. agree.. you can also use the author attribute of the comment object to find who added the comment.

  3. JP says:

    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.

    • Jal627 says:

      I would like to put many comments on one workbook and then have a single cell populate them as a list. With getComment() I can only query one cell. How can I get this to query multiple cells and populate those comments to just one cell?

  4. Chandoo says:

    @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…

  5. Gerald Higgins says:

    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 ?

  6. Chandoo says:

    @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 ?

  7. JP says:

    Add the following line to the UDF, right after ‘On Error Resume Next’:

    Application.Volatile True

    Then press F9 to recalc as needed.

  8. Gerald Higgins says:

    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.

  9. JP says:

    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.

  10. Gerald Higgins says:

    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 !

  11. JP says:

    Makes sense. Updating a cell (not the comment, unfortunately) would trigger recalculation.

  12. JASC says:

    Hola
    muy interesante todo lo que he visto y justo ahora lo necesito. He hecho cada paso descrito (Excel 2007) pero no pasa absolutamente nada? hay algo en la configuracion de Excel que debo cambiar para que esto funcione? algo en especial para el uso de getcomment? Me aparece en mis UDF y la escribo en una celda por ejemplo en A2 escribo =getcomment(A1) y en A1 he puesto texto, numeros, formulas enlazando otras celdas, etc y no sucede absolutamente nada, ni siquiera se general el comentario, genero el comentario manual y al comentario no le sucede nada, nunca muestra nada.

    Alguna idea de por que me sucede esto?

    Gracias

  13. Tim Lyle says:

    Does anyone know of a formula that would reference information and match it with corresponding cells and add the information as a formula?
    ex. i have a whole spreadsheet of codes like KDILAOP and UYDLNNI. Everytime there is a KDILAOP on the spreadsheet, I would like a comment inserted explaining what that code means.
    This is beyone my excel abilities.

  14. Hui... says:

    In another Blank cell in the same row as KDILAOP
    and assuming KDILAOP is in A10
    put an equation like =If(A10=”KDILAOP”, “My Message”,””)
    .
    This can be extended by =If(A10=”KDILAOP”, “My Message”,If(A10=”UYDLNNI”, “My Message 2″,””)
    “”)
    .
    Beyond this you should have a look at using a Table of velues and associated Comments
    Then use a lookup function to retrieve the right value.
    refer: http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

  15. ElleCubed says:

    Thank you, thank you, thank you!

    You have just made my life much easier. I need to learn more about VBA and Macros – they are amazing.

  16. Erica says:

    Thank you!!!! This really really helped me!

  17. Mike A. says:

    Any way to extract a comment to a cell WITHOUT using VBA? Unfortunately MS extracted the VBA utility from my Excel For Mac 2008.

  18. Mike A. says:

    Hul,

    I appreciate your detailed response :). President “Silent Cal” Coolidge was at a party when a guest stepped up to him and said, “I bet my friend $100 I can get you to say more than two words. The President replied, “You lose.”

    How about this: again, without using VBA, is there a way to have Excel randomly select a cell from an array and duplicate that cell in another cell, INCLUDING any comments associated with that cell?

    Thanks,
    Mike A.

  19. Hui... says:

    The Random part yes, using a Named Formula
    The comment bit, No
    If you can post/email me the Array/Range I’ll send back a response

  20. Mike A. says:

    It might be better first to explain the purpose, you may have an easier solution.

    I have an array in which some cells contain a word, and in those cells a comment contains that word’s definition. I would like to use the array essentially as a deck of flash cards, where, when the user does a recalculation, Excel randomly selects a cell and takes the user to that cell so he/she can see the word.

    The user tries to recall the definition, and then has the option of displaying the comment, the same way he/she might flip a card to check his/her memory. (I wanted to display the comment with a button click, but the lack of VBA evidently rules out that option.)

    Ideally, the formula should ignore any blank cells, of which there are many in the array.

    Assume the array is defined as $A$4:$Z$200; though it might be better to use a name for the array as I expect it will expand over time, and a name should (yes???) obviate the need to change the formula, I will just need to redefine the array. If this makes sense, call the array WordNerd.

    Thanks!

  21. Hui... says:

    What about putting the comments on a Hidden Sheet in the same location

    eg:
    Sheet1!A10: Dictionary
    Sheet2!A10: A book of word definitions
    Then hide sheet 2
    Use formulas in B10, C10 to retrieve the answer

  22. Mike A. says:

    Thanks, but every day I add 10-20 words, this sounds like I’d have to keep both sheets in synch. Plus I already have a 1,000 or so entries I’d have to retype.

    Isn’t there any way that Excel can randomly select a cell from an array and simply take me to it, without VBA???

    • Hui... says:

      Yes Mike, Excel can do exactly that
      But you keep telling us that your comments are in cell comments
      I’m suggesting that Excel can do that without VBA if you have the Description in Cells, not in Cell Comments
      You could put the comments off to the side like Column CA, where the user won’t see them

      You could also send the file to a Windows user and get the comments extracted to cells with some simple VBA

      Sub Extract_Comments()
      Dim c As Range
      For Each c In Range("A1:A1000") 'Change range to suit
      If c.Offset.Value "" Then c.Offset(, 1).Value = c.Comment.Text
      Next
      End Sub

  23. Saurabh Parte says:

    Formula to get the comments excluding user -

    =CLEAN(MID(A1,FIND(“:”,A1)+1),300))

  24. Madhukar says:

    I can not belive that it would as simple as it is. You save my lot of time. Many Thanks :)

  25. Rama says:

    Thanks Chandoo. Formula helped.

  26. Phil says:

    I’ve created the Function (getComment) in personal.xls.
    How do I get it to be able to be used in any other workbook?
    Is creating it in a new workbook then saving as an add-in the only way?
    Thanks

  27. Paulo says:

    hey guys! :)

    I´d like to know how to write a comment in a cell and this comment be shown automatically in another cell as a comment, not as a text in the cell.

    I’m a beginner and I´m already crazy looking for this vba. Please, help me!! :)

    ThankS!! 

    • Hui... says:

      @Paulo
      Is there any relationship between the addresses of the two cells?

      • Paulo says:

        yes! I am building a table of daily study.

        On day 1, for example, the table is “study” where the subject is math and trigonometry is the comment.

        On day 2, the table is “Review.” On this cell is an exact copy of the study table. Math as a value an  trigonometry as a comment. All this automatically.

        At first there is nothing wrote on the Review cell.

        I hope I’ve ben clear… I’m not too good in English. sorry…

        How can I send you an exemple picture ??

  28. Prasad says:

    Wow, you made my day. Can’t thank enough!

  29. Stephanie says:

    I tried the code to add a date to the comment, and it didn’t work.  I looked at the code and don’t see anything for date coding.

  30. Ron says:

    I just wanted to drop a quick “thank you”.  The UDF tip saved me some serious time on this specific project I just finished up. 
    Cheers.

  31. Jasper Tsai says:

    You are……… I am so grateful right now I don’t even know what to say, you are a AWESOME!!!

  32. It is great. I required it. My client is sending address field in comment under name column.

    now it became very easy.

    Thanx.

  33. Eeva says:

    This was brilliant – big thanks for sharing! :)

  34. Nishant says:

    Thanks so much Chandoo. It’s fantastic. However, I’ve inserted picture as comment in the cell. As this function returns string, it’s not going to return picture from comment. Is there anyworkaround to retrieve picture from comments.
    Any help would be much appreciated.

  35. Alice says:

    Thank you, this was so easy, straightforward and nice, it almost made me believe I can code.

  36. Dominic says:

    This is great. So easy to do, and its my first time to use udf.

Leave a Reply