How to Convert Comments to Cell Contents in Excel

This post will guide you how to convert comments to cell contents in Excel 2010/2013/2016. How do I extract cell contents with VBA Macro in Excel. How do I use a formula to extract comments in a given range in Excel.

Convert Comments to Cell Contents with VBA Macro


Assuming that you have a list of data in range B1:B4, and each cell contain one comments. And you want to convert all comments to cell contents in Excel. Or you want to extract cell comments from a given cell. How to do it. You can use an Excel VBA Macro code to achieve the result. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

convert comments to contents1

Sub ExtractCellContents()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Range that you want to extract cell comments:", "ExtractCellContents", myRange.Address, Type:=8)
    For Each oneCell In myRange
        oneCell.Value = oneCell.NoteText
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

convert comments to contents2

#6 Select One Range that you want to extract cell comments. Click Ok button.

convert comments to contents3

#7 let’s see the result:

convert comments to contents4

Convert comments to Cell Contents with User Defined Function


You can also write a User Defined function to achieve the same result of converting comments to cell contents in Excel. Here are the steps:

#1 repeat above steps 1-3.

#2 paste the below VBA code into the code window. Then clicking “Save” button.

convert comments to contents5

Function ExtractComments(oneCell As Range) As String
    If Not oneCell.Comment Is Nothing Then
        ExtractComments = oneCell.Comment.Text
    End If
End Function

#3 back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=ExtractComments(B1)

#4 drag the AutoFill handle over other cells to apply this formula to extract comments.

convert comments to contents6

 

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar