I believe this is possible only with VBA. Try the ff. user-defined function:
Public Function GetComment(c As Range) As String
If c.Comment Is Nothing Then
GetComment = "?"
GetComment = Replace(c.Comment.Text, c.Comment.Author & ":", "")
To create the UDF:
- Press CTRL + F11.
- Paste the code above.
- Close the VBA editor.
If you'd like to show the comment author's name as well, replace the 5th line with:
GetComment = c.Comment.Text
To use the function, enter the ff. into a cell in the column where you want to place the comments and then copy it down.
B1 is a cell that contains the comment you want to get
Here's an example of how to use it: