An example, putting the comment text in column J:

Sub ExtractComments()
Dim Cell As Range
On Error GoTo EndThis
For Each Cell In Range("A1:A10").SpecialCells(xlCellTypeComments)
Cell.Offset(0, 9).Value = Cell.Comment.Text
Next
Exit Sub
EndThis:
MsgBox "No comments"
End Sub

I'm not sure what you're going to do if more than one column has comments.

--
Jim Rech
Excel MVP