Extract Comment Text from Excel Cell: 3 Easy Methods (+Download)

Exctract the Comment and Note Text in Excel

Many Excel users like to use the comment function in Excel. Unfortunately, they come with some disadvantages (displaced, wrong size, hiding content, bad for printing, often outdated, e.g.) so that I usually recommend not to use them. Instead, I recommend using a comment column. This article describes how to transform Excel comments to cells. Extract comment text or note text of an Excel cell with these 3 methods.

Comments are not comments any longer

The "traditional" Excel comments are now called notes.
The “traditional” Excel comments are now called notes.

With Excel 2019, comments are not called comments in Excel any longer. The “traditional” comments as you probably know them with the yellow background color are now called “Notes”.

But Excel still provides “Comments” (additional to the “Notes”). Instead of just having simple text “notes”, “comments” can now be discussions and other users can leave replies.

Initial advice

Because you are reading this you probably experienced the disadvantages of Excel comments and notes. So, my basic advice for you:

recommendation, professor, excel

Never use the Excel built-in comments or notes!

Instead insert a column (or row) and leave your comments there.

3 Methods for pulling out comment or note texts in Excel

Before we start: Excel doesn’t provide a built-in formula to extract the text from a comment or note. That said, we have to come up with a work-around.

Method 1: Extract comment text or note text to cell manually

Method 1: Copy and paste or type the comment / note text manually.
Method 1: Copy and paste or type the comment / note text manually.

If you just have a few comments to read out, it might be the fastest to manually type or copy and paste the text. Using some Excel functionality can speed up this process.

  1. Display all comments at once. For “Notes” (in Excel versions until 2016 “Comments”) go to “Review” and click on “Show all Notes”. For the new comments since Excel 2019 go to “Review” and click on “Show Comments”.
  2. You can also select all cells having comments (and highlight them e.g.). Go to the “Home” ribbon and click on “Find & Select” on the right-hand side. Click on “Go to Special” and then select “Notes” (or “Comments”) and confirm with OK.

Method 2: Use a VBA macro to transform comments to cells in Excel

Extract comment text with a simple VBA code.
Extract comment text with a simple VBA code.

As so often with rather tricky problems in Excel, VBA can help. In this case, just 3 lines of code could be the solution.

Follow these steps for note texts (before: comments – the yellow once and the red cell corner).

  1. Insert a new VBA module (please refer to this article and scroll down to “How to insert a new VBA module manually” if you need more information).
  2. Copy and paste the following codes.
  3. Back in Excel, type =ProfessorExcelReturnNoteText(B5) if you want to return the number format code from cell B5.
Function ProfessorExcelReturnNoteText(cell As Range)
    ProfessorExcelReturnNoteText = cell.Comment.Text
End Function

For the new comments, please use these lines of code and copy them to your VBA module. In your Excel cell, you enter =ProfessorExcelReturnCommentText(B5).

Function ProfessorExcelReturnCommentText(cell As Range)
    ProfessorExcelReturnCommentText = cell.CommentThreaded.Text
End Function

Because the new comments can contain “conversations” with replies e.g., you can also extract the whole conversion. Use the following code. In your Excel cell, enter =ProfessorExcelReturnCommentTextReply(B5,1) for the first reply. For the second reply, type =ProfessorExcelReturnCommentTextReply(B5,2).

Function ProfessorExcelReturnCommentTextReply(cell As Range, Optional replyNumber As Long)
    ProfessorExcelReturnCommentTextReply = cell.CommentThreaded.Replies(replyNumber).Text
End Function

Method 3: Use Professor Excel Tools to extract comment text or note text

The most comfortable way to return the number format code is probably using an Excel add-in. I – of course – recommend my own add-in. You can download the free trial and even after expiring, the formula features still work.

If you use the Professor Excel Tools add-in, just type

  • =PROFEXNoteText(B5) for the note (old / yellow comment text),
  • =PROFEXCommentText(B5) for the new threaded comments,
  • or =PROFEXCommentReplyText(B5,1) for receiving the first reply text.

If you want to extract the author names of threaded comments, use

  • =PROFEXCommentAuthorName(B5) for the author name of the original comment or
  • =PROFEXCommentReplyAuthorName(A7,1) for the author name of the first reply.

That’s it!


Professor Excel Tools Box

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.


Download example workbook

Download an Excel file with examples for all three methods of extracting comment texts in Excel.
Download an Excel file with examples for all three methods of extracting comment texts in Excel
download, arrow

Please feel free to download the example used in this article in this workbook. Click here and the download starts.

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

7 comments

  1. Hi… i extract the comments but when i put in a new input on the comments .. its not auto generate even tho under options calculation is put under “automatic”

    1. Hi Rayden,
      yes, that’s a problem. Usually it works if you hold Ctrl + Alt and press F9 on the keyboard to force a full calculation.
      Best regards,
      Henrik

  2. Thanks!… your page helped me figure out how to get the comment out using Cells()… I was trying String=Cells().Comment… didn’t realize I needed to add .text… Saved me a lot – Thanks!

  3. I need to extract comments to cells in Excel 2019. I downloaded Professor Excel, but cannot figure out how to use it for this. If I type =PROFEXCommentText(cell reference) in the cell where I want the comment text to appear, it tells me “there’s a problem with this formula”. What am I doing wrong?

  4. Helle, and thanks for the formulas, its a great helt…

    I did encounter a problem, i have som notes that comes with som line changes, but when it comes out in the formula the cell just show one long line with the comment, is there any way to get the line changes from the comment or note in the cell also ?

Leave a comment

Your email address will not be published. Required fields are marked *