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

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

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:

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

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

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!

Download example workbook

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