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:
- Convert Cell Contents to Comments
- 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:
If there are user names in the comment, use this formula to strip them off automatically: