Skip to main content

How to convert comments to cell contents in Excel?

Supposing I have a range cells filled with comments in a worksheet, and now, I want to convert all of the comments to cell contents so that I can print them neatly and roundly. Are there any good ways to solve this task?

Convert comments to cell contents with User Defined function

Convert comments to cell contents with VBA code

Convert comments to cell contents with Kutools for Excel


arrow blue right bubble Convert comments to cell contents with User Defined function

By using this method, you need to create a defined function first, and then apply the defined formula to convert the comments to cell contents. You can finish it as the following steps:

1.Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following function into the Module:

Function GetComments(pRng As Range) As String
'Updateby20140509
If Not pRng.Comment Is Nothing Then
    GetComments = pRng.Comment.Text
End If
End Function

2.Press Ctrl + S to save the function.

3.Then input the following formula “=GetComments(A1 )” in any blank cell, in this case, I will use cell E1. See screenshot:

doc-comments-to-cells1

4. Press the Enter key. And select cell E1, then drag the fill handle over the range of cells that you want to contain this formula. And all of the comments are converted to cell contents. See screenshot:

doc-comments-to-cells2


arrow blue right bubble Convert comments to cell contents with VBA code

If you know the VBA code, the following brief code also can help you to convert comments to cell contents.

1. Select the range that you want to convert to cell contents;

2. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:

Sub CommentToCell()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = Rng.NoteText
Next
End Sub

3. Then click  doc-multiply-calculation-3 button to run the code. And select a range you want to convert then click OK, and all of the selected comments have been converted to cell contents in its relevant cells.


doc-comments-to-cells3 -2 doc-comments-to-cells4

Note: If you don’t want the comments, you can delete them.


arrow blue right bubble Convert comments to cell contents with Kutools for Excel

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.Get it Now .

The Convert Comment and Cell of Kutools for Excel is a handy and useful tool can help you to solve many problems about comments.

After installing Kutools for Excel, please do as follows:

1. Select the range that you want to import the comments into the cells.

2. Click Kutools > More > Convert Comment and Cell, see screenshot:

doc-comments-to-cells-5

3. In the Convert Comment and Cell dialog box, select Convert comments into content of cells. See screenshot:

doc-comments-to-cells-6

4. Then click OK. Now all of the selected comments are converted to cell contents in its original range.

For more detailed information about Convert Comment and Cell, please visit Convert Comment and Cell feature description.


Related articles:

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (24)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, The first function in a module does the job only once: when I write or drag the formula down. If I go back to change any of the comment it does not update the destination cell with the new comment. For example let's say I have a comment in A1, then when I place the formula in cell B1, it will show me the A1's comment only the first time. If I go back and change the A1 comment, the cell B1 does not get updated. I tried "Calculate Now" or F9 and nothing happens. I saved the file and opened again and nothing, cell B1 stays with the old comment. It is like the function is dead. Now, if I enter or change anything in the cell A1 itself (not the comment), like adding an space in the end, it will immediately update the comment. I'm using Office 2019 Pro.
This comment was minimized by the moderator on the site
thanks very musch
This comment was minimized by the moderator on the site
great site
This comment was minimized by the moderator on the site
is there a way to do this without the title of a comment or the non bold writing?
This comment was minimized by the moderator on the site
Great tip, thanks for sharing!
This comment was minimized by the moderator on the site
Perfect! Thank You
This comment was minimized by the moderator on the site
You are a star!!! thanks
This comment was minimized by the moderator on the site
It does not automatically update when I change the comment. How do I change this? Thanks
This comment was minimized by the moderator on the site
Same problem here. Changing the comment does not trigger the calculations. Forcing the calculation (F9) does not work. Saving and load the file does not work. However, changing a cell content will trigger the calculations.
This comment was minimized by the moderator on the site
You are my HERO!!!!!!!!!!!!!!!!!!!!!! Thank You Thank You Thank You
This comment was minimized by the moderator on the site
Hi. I tried this code and it worked right. But I have a little problem: can I use this code combined with VLOOKUP? I tried but I didn't get anything. If you can help me, I'll be thankful. I'm brazilian. If I wrote something wrong, I apologise. Thanks!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations