How to convert comments to cell contents in Excel?

In many situations, you may have a worksheet filled with important information stored within cell comments (or "Notes" in newer versions of Excel). However, comments are not always directly visible or printable in a standard worksheet printout, and managing or extracting comment data for sharing, archiving, or processing can be inconvenient. To address this, you might need to transfer or convert all comments into the actual contents of their respective cells. This ensures information becomes directly accessible, viewable, and printable along with other cell data. If you're searching for effective ways to convert comments to cell contents in Excel, several methods are available, each suited to different levels of Excel proficiency and requirements.

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

Convert comments to cell contents with User Defined function

One practical way to extract comments into cells is to use Excel’s User-Defined Function (UDF). This approach is ideal for users with basic VBA knowledge who prefer not to run macros that directly alter worksheet content, as it offers greater control and flexibility. The UDF returns the comment’s text to any cell you choose without overwriting the original data.

To convert comments to cell contents using this method, you'll need to create the UDF first, then apply it as a formula. Please follow these steps:

1. Click the Developer > Visual Basic to access the Microsoft Visual Basic for Applications window.  In the VBA window, click Insert > Module, and paste the following function into the new 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. Save your VBA Project using Ctrl + S or via the menu. If prompted, save your workbook as an Excel Macro-Enabled Workbook (*.xlsm) to ensure the UDF will work properly.

3. Now, in any empty cell where you want to display the comment text, enter the formula =GetComments(A1) (replace A1 with the actual cell reference whose comment you want to extract). See screenshot:

4. Press Enter. The cell will display the comment from cell A1 . To extract comments from an entire range, drag the fill handle (small square in the lower right corner of the cell) down or across to apply the formula to other cells referencing the corresponding cells in your dataset. All comments will be converted to cell contents accordingly. See screenshot:

 Convert comments to cell contents with VBA code

For users who need to perform the conversion automatically or for a large batch of cells, a short VBA macro can efficiently copy all comments into their corresponding cells as plain text. This method is direct and fully automated, but be aware that it will overwrite any existing cell content in the target cells, so always back up your data first.

1. First, select the range where you want to convert comments to cell contents. Selecting a smaller range reduces the risk of accidental data loss.

2. Go to the Developer tab and choose Visual Basic to open the Microsoft Visual Basic for Applications window. Then click Insert > Module, and copy the following code into the Module window:

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. After pasting the code, click the  Run button in the VBA editor (or press F5) to execute the macro. The code will typically prompt you to select the range—ensure you select the correct cells. Click OK to proceed, and all existing comments in the selection will be copied into their corresponding cells, replacing the cell’s contents. The following screenshot illustrates the before-and-after result:

This VBA macro solution is best for users comfortable running macros and needing to fully automate the comment-to-content transfer process. The main advantage is speed and the ability to batch process large areas, but as it overwrites existing data, always use caution and back up important files before running the macro.

Convert comments to cell contents with Kutools for Excel

The Convert Comment and Cell tool in Kutools for Excel provides a user-friendly and efficient way to quickly move comment text into cell contents. This utility not only saves significant time but also minimizes manual operations, reducing the risk of errors during the process.

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

After installing Kutools for Excel, you can follow these simple steps to convert comments to cell values:

1. Highlight the cell range containing the comments you wish to convert to actual cell content.

2. On the Excel ribbon, click Kutools > More > Convert Comment and Cell.

3. In the Convert Comment and Cell dialog box, choose Convert comments into content of cells. This operation will transfer each cell's comment/note directly into the cell value itself. See the screenshot below:

4. Click OK. Instantly, all chosen cells have their comments placed into the respective cell's content, and the worksheet is updated accordingly.

This approach is particularly beneficial for users who prefer a graphical interface and minimal manual effort. It is fast, reliable, and ideal for users who do not wish to use VBA or Excel formulas. For more comprehensive instructions about the Convert Comment and Cell tool, visit the Convert Comment and Cell feature description page.

If you encounter issues where cell content is being overwritten, it's advisable to back up your worksheet prior to conversion, or work on a duplicate sheet. Kutools also provides options for converting comments back and forth for flexible management.

Tips, troubleshooting, and summary suggestions

When choosing a solution, consider the trade-offs: using VBA is very flexible but requires some technical skill and data backup precautions. Kutools offers a no-code and efficient route, ideal for regular users who value simplicity. User Defined Functions allow selective extraction without altering original cells but require formula application and may not handle new-style threaded comments.

Common troubleshooting points include:

• If your macro or formula doesn’t return the expected comment content, check if you’re working with threaded "Comments" (not "Notes")—many solutions only work with classic notes.
• If using Office 365 or Excel 2019+, right-click a cell and select "Show/Hide Notes" to confirm you've got the correct comment type.
• Always save your file before applying macros or batch conversions, to prevent accidental data loss.
• After extracting values, if you wish to keep the worksheet tidy, you can remove comments by selecting the cells, right-clicking, and choosing "Delete Comment".

Related articles:

• Change all comment formats in cells
• Change all comments' author name
• List all comments to a new worksheet or workbook
• Find and replace text within comments
• Show or hide all comments and comment indicators in Excel
• Convert cell contents to comments in Excel