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
• Convert comments to Cell Contents with User Defined Function

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.

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

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

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

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.

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

#7 let’s see the result:

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.

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.