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
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:
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.
#4 drag the AutoFill handle over other cells to apply this formula to extract comments.