Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have one column in a table where (almost) each cell in that column has a comment (red triangle), I would like to move all the comments into a new column.

Example of what I have:

Col.1
MIT (comment: MA)
Harvard (comment: MA)
Yale (comment: CT)
NYU (no comment)

What I would like to have:

Col.1, Col.2
MIT, MA
Harvard, MA
Yale, CT
NYU, ?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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 = "?"
    Else
        GetComment = Replace(c.Comment.Text, c.Comment.Author & ":", "")
    End If
End Function

To create the UDF:

  1. Press CTRL + F11.
  2. Paste the code above.
  3. Close the VBA editor.

Note:
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.

=GetComment(B1)

Where:
B1 is a cell that contains the comment you want to get

Here's an example of how to use it:

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.