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

Comments to Cells – 3 Methods for Comments and Notes (+Download)

posted on 22 Oct 19

comments 0

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.

Contents

Comments are not comments any longer
Initial advice
3 Methods for pulling out comment or note texts in Excel
Download example workbook

Comments are not comments any longer

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:

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.

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.

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”.
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.

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).

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).
Copy and paste the following codes.
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

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

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

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!

Comments to Cells – 3 Methods for Comments and Notes (+Download)

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

Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.

Download example workbook

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

Please feel free to download the example used in this article in this workbook.

Was the information helpful in this article?

If yes, why don't you subscribe to our free, monthly Excel newsletter?
If no, please let us know what to improve.

Henrik Schiffner

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.