

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
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.
Because you are reading this you probably experienced the disadvantages of Excel comments and notes. So, my basic advice for you:
Instead insert a column (or row) and leave your comments there.
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: 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.
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).
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
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
If you want to extract the author names of threaded comments, use
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 an Excel file with examples for all three methods of extracting comment texts in Excel.
Download
Was the information helpful in this article?
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 6
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”
Henrik
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
Mr. Owen Sneath
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!
Greg Stricker
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?
Henrik Schiffner
Hi Greg,
I’ve sent you an e-mail. Let’s figure it out.
Best regards,
Henrik
Bent Christensen
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 ?