Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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:



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Casey Norman · 2 years ago
    You are my HERO!!!!!!!!!!!!!!!!!!!!!! Thank You Thank You Thank You
  • To post as a guest, your comment is unpublished.
    Henrique · 2 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Szpepa · 2 years ago
    It's worked :)
    Thanks!
    Good job
  • To post as a guest, your comment is unpublished.
    Graham · 2 years ago
    The 1400 cell comments took but a few minutes. Wow!
    Thank you.
  • To post as a guest, your comment is unpublished.
    Arul · 2 years ago
    good job thank u so much