Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .