Convert Excel Cell Contents to Comments and vice-versa with VBA Code

Suppose there’s a database in an Excel worksheet, and now you want to convert all the cell contents in a sheet to cell comments. You could manually do this by copy-pasting, or avoid the labor by simply running a macro code to automate this task.

In this post, we will see two sets of code:

1. Convert Cell Contents to Comments
2. Convert Comments to Cell Contents

So, let’s get started.

1) Convert Cell Contents to Comments

First, we have to insert the code to a VB Module. To do that, launch “Microsoft Visual Basic for Applications” by pressing Alt + F11, or navigate to Developer tab > Visual Basic. Go to Insert > Module, and paste the code:

'Convert Cell Content to Comment
Sub ConvertToComment()
Dim C As Range
For Each C In Selection
    C.ClearComments
    If Len(C.Value) > 0 Then
        C.AddComment
        C.Comment.Text C.Value & ""
    End If
    'Optional: Delete cell content after converting
    C.ClearContents
    Next C
End Sub

Now close the VB Editor window or switch back to Excel.

Highlight the cells you wish to convert to comment, then run the macro by going to Developer tab > Macro > Select ConvertToComment > Run.

This will copy the cell content to cell comment, and delete the original cell content.

Note: You can comment out or remove C.ClearContents if you wish to keep the cell content.

2) Convert Comments to Cell Contents

Now we will look at how to reverse what we did above. The below VBA code snippet will let you convert a cell comment to cell content. Doing so would also delete the comment from the corresponding cell.

Just like above, insert the below code into a VB module to use.

'Convert Comment to Cell Content
Sub ConvertToCell()
Dim C As Range
For Each C In Selection
    C.Value = C.NoteText
    'Optional: Delete comment after converting
    C.ClearComments
    Next C
End Sub

Note: Comment out C.ClearComments if you wish to keep the comment intact.

You could also fetch comment from a cell and use it anywhere in your Excel Sheet using a custom function. Just add below code in a VB Module:

'Fetch Comment from a cell
Function FetchComment(val) As String
On Error Resume Next
FetchComment = val.Comment.Text
End Function

Now call this function on any desired cell just like a regular excel formula:

=FetchComment(A1)

If there are user names in the comment, use this formula to strip them off automatically:

=RIGHT(getcomment(A1),LEN(getcomment(A1))-FIND(":",getcomment(A1)))