Convert Comments to Notes in Excel (3 Suitable Ways)

The Comment is one of the latest features of Microsoft Excel. The comment was first introduced in Microsoft Excel 2016. In newer versions, comments have the option to reply and the previous comment feature is now known as notes.
If you want to convert your Comments into Excel Notes, we have 3 methods that can help you in doing so. They will copy the comments and paste the contents in Excel cells, though not actually creating Excel notes. If this is handy enough for you, please read this article to know all the 3 methods.


Download Practice Workbook

Download this sample workbook from the following button, to practice while you are reading this article.


3 Ways to Convert Comments to Notes in Excel

Before starting to describe the methods, let’s first understand the dataset. Look at the following image. We have a dataset of some employees in a company. We can see that each cell of the Name column contains comments on the employee (A purple symbol indicates the existence of the comment on a cell).

Comments in Cells


1. Convert Comments Manually

If your dataset doesn’t contain that many comments and copying them and pasting them manually seem easier than applying VBA codes or any other more complex procedure, we will do it!

It’s worth mentioning that typing speed is a crucial factor in copying comments manually. Now, just carry out the following steps. 👇

📌 Steps:

  • Move the cursor to the cells of the Name column. Comments are visible now as the pop-up.

Convert Comments Manually

We can see the comments, user name, and time on the pop-up.

  • Now, click on the Review tab.
  • Choose the Show Comments button from the Comments group.

Convert Comments Manually

  • Look at the following image now.

We can see a new section named comments appear on the right side. Comments are shown with details here.

  • Now, add a column named Comments on the right side of your dataset.
  • Type the comments of the corresponding cells of the Name column. Or select, copy and paste the comments into the destination cells.

All comments are pasted/typed here in a new column now.

Read More: Difference Between Threaded Comments and Notes in Excel


Similar Readings


2. Create a User Defined Function to Convert Comments to Notes

In this method, we will create a function based on VBA Macros. This will extract text from the comments and paste it into Excel worksheets in the specified cells, as notes for you. See the steps below.

📌 Steps:

  • First, go to the Sheet Name section at the bottom of the worksheet.
  • Press the right button of the mouse on the sheet name.
  • Choose the View Code option from the Context Menu.

User Defined Function to Convert Comments to Notes

  • We enter the VBA window.
  • Choose Module from the Insert tab.

  • This is the VBA module window. We will write and run VBA code from here.

  • Copy and paste the following VBA code here.
Function Get_Text_from_Comments(cell As Range)
Get_Text_from_Comments = cell.CommentThreaded.Text
End Function

User Defined Function to Convert Comments to Notes

  • Save the code by pressing Ctrl+S.
  • Now, press Alt+Q to close the VBA window.
  • Now, go to Cell F4 of the Comments column.
  • Put the formula below.
=Get_Text_from_Comments(B4)

User Defined Function to Convert Comments to Notes

This formula has been created by using the VBA code.

  • Press the Enter button.

We get the comment of the corresponding cell.

  • Pull the Fill Handle icon downwards.

Now, get comments on all the cells of the Name column.

Read More: How to Add Notes in Excel (The Ultimate Guide)


3. Apply a VBA Code to Convert Comments to Notes

In this section, we will use a simple VBA code that will convert the comments into notes with one click.

📌 Steps:

  • Go to the VBA command module.
  • Copy and paste the VBA code below.
Sub Convert_Comment_to_Text()
Dim range_1, work_range_1 As Range
On Error Resume Next
Set work_range_1 = Range("B4:B8")
For Each range_1 In work_range_1
range_1.Offset(0, 4).Value = range_1.CommentThreaded.Text
Next
End Sub

VBA Code to Convert Comments to Notes

  • Now, press the F5 button to run the code.

Here, corresponding comments of the Name columns are shown in the Comments column.

🔎 Code Explanation:

Dim range_1, work_range_1 As Range

This declares the variable.

On Error Resume Next

If any error is found, then it will proceed to the next section.

Set work_range_1 = Range("B4:B8")

Select a range as the value of the work_range_1 variable.

For Each range_1 In work_range_1

Apply a for loop.

range_1.Offset(0, 4).Value = range_1.CommentThreaded.Text

Get the comments from the range_1 and set those values 4 columns right side of the existing location.

Read More: How to Add Notes in Excel Chart (2 Easy Methods)


Conclusion

In this article, we described 3 methods to convert comments to notes in Excel. If you want to avoid the manual process, then apply the VBA Macros. I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo