Extract comments (Not notes) from excel

IamLost

New Member
Joined
Jan 8, 2013
Messages
16
Hello, I have looked through the search and cant find anything, also google is not helping. I have inherited an excel spreadsheet which has a lot of data as well as a lot of comments. I am looking for a way to extract the comment into a new cell. Everything I read online refers to the "old" comments which are now called Notes I believe.

To simplify, I have a spreadsheet with for e.g. Column A has information in the cell as well as a comment. What I want to be able to do is run a macro to extract or copy the information in the comment and paste this in the cell to the right.

Any help would be much appreciated.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
964
Office Version
  1. 2016
Platform
  1. Windows
Have a look here. Let us know if you need further assistance

 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,403
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Looks down column A for comments assuming you also have some values in column A
VBA Code:
Sub My_Commnets()
'Modified 12/14/2020  8:03 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        If Not Cells(i, 1).Comment Is Nothing Then Cells(i, 2).Value = Cells(i, 1).Comment.Text
    Next
Application.ScreenUpdating = True
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub IamLost()
   Dim Cmt As CommentThreaded
   For Each Cmt In ActiveSheet.CommentsThreaded
      Cmt.Parent.Offset(, 1).Value = Cmt.Text
   Next Cmt
End Sub
 
Solution

IamLost

New Member
Joined
Jan 8, 2013
Messages
16

ADVERTISEMENT

Have a look here. Let us know if you need further assistance

Hello, Thank you for that, I had a read through and tried the code but it doesnt work. When i delete the comment and try a note, it does provide a popup with the text. But nothing happens when I have a comment. Not sure if its my terminology I have wrong or not?
 

IamLost

New Member
Joined
Jan 8, 2013
Messages
16

ADVERTISEMENT

Try this:
Looks down column A for comments assuming you also have some values in column A
VBA Code:
Sub My_Commnets()
'Modified 12/14/2020  8:03 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        If Not Cells(i, 1).Comment Is Nothing Then Cells(i, 2).Value = Cells(i, 1).Comment.Text
    Next
Application.ScreenUpdating = True
End Sub
Hello similar to my previous reply, this code works perfect when its a "note" and not a "Comment. Again apologies as I may be getting the two confused or describing it wrong.

1607955439766.png
 

IamLost

New Member
Joined
Jan 8, 2013
Messages
16
No your terminology is correct, you are talking about the new Threaded comments.
Thank you. For your code, when I try this I get an error message saying 400 or if I enter as a module, I gert Run time error 1004. Sorry my knowledge of VBA is poor at best.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
When you put the code in a standard module, what is the error message that you get?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,403
Office Version
  1. 2013
Platform
  1. Windows
Hello similar to my previous reply, this code works perfect when its a "note" and not a "Comment. Again apologies as I may be getting the two confused or describing it wrong.

View attachment 27911
You subject Title says:
Try this:
Looks down column A for comments assuming you also have some values in column A
VBA Code:
Sub My_Commnets()
'Modified 12/14/2020  8:03 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        If Not Cells(i, 1).Comment Is Nothing Then Cells(i, 2).Value = Cells(i, 1).Comment.Text
    Next
Application.ScreenUpdating = True
End Sub
You subject Title says: Extract comments (Not notes) from excel
My script looks for comments not notes.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,776
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top