Extract Comments and place as a value in another cell

[Kathleen_0422]

Hello All,

I am working with Excel 2003

My Questions is:

I have a large spreadsheet close to 17000 rows. In column K there are comments embedded within the cells.

I would like to copy the text of the comment and place it in Column A on the same row. Is there a formula I can use to do so?

Thanks for your help

 

[crook_101]

Hi Kathleen & Welcome to the Board,

A UDF will perform this task for you:

[FONT=Arial]Function GetCommentText(rCommentCell As Range)[/FONT]
[FONT=Arial]Dim strGotIt As String[/FONT]
[FONT=Arial]   On Error Resume Next[/FONT]
[FONT=Arial]   strGotIt = WorksheetFunction.Clean _[/FONT]
[FONT=Arial]             (rCommentCell.Comment.Text)[/FONT]
[FONT=Arial]   GetCommentText = strGotIt[/FONT]
[FONT=Arial]   On Error GoTo 0[/FONT]
[FONT=Arial]End Function[/FONT]

In A1 type =GetCommentText(K1)

Post back if you need anything further
HTH
Ian

 

[VoG]

You will need a macro for that. Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste into the white space on the right

Sub cmts()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
    If cmt.Parent.Column = 11 Then cmt.Parent.Offset(, -10).Value = cmt.Text
Next cmt
End Sub

Press ALT + Q to close the code window, press ALT + F8, click on cmts then click the Run button.

 

[crook_101]

Peter's is the best option.....doffs cap

 

[Kathleen_0422]

Thank you very much. I have it running now.