Comments to Cells – 3 Methods for Comments and Notes (+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.
Contents
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.
|
1 2 3 |
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).
|
1 2 3 |
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).
|
1 2 3 |
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!
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.
Comments 1
Rayden
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”