Excel has a very useful feature called “cell comments” using which you can add comment to a cell. This is a very good way to gather remarks and review comments when a workbook is shared with colleagues and others. But what if you have typed a ton of cell comments and now want a way to extract them and do something with that data?
Well, no need to select each comment and copy the contents. You can use a simple user defined formula (UDF) to do just that. Here is a one line formula that I wrote.
Function getComment(incell) As String
' aceepts a cell as input and returns its comments (if any) back as a string
On Error Resume Next
getComment = incell.Comment.Text
End FunctionTo make it work,
To make the getComment() UDF work for you, you need to install it first. Here is a step by step guide if you dont know how to install user defined formulas in excel.
The simple and easy version:
- Copy the above vba code
- Open a new excel file
- Right click on any sheet tab name and select “view code” option.
- Don’t be scared of the VBA editor. Take a deep breath.
- Click on “Insert” menu option and select “Module”
- Paste the code in the new module
- Save the workbook and start using the getComment() formula.
The lengthy and more stable version:
- Same as first 6 steps above.
- Press ctrl+s to save the work book. Specify “excel add-in” as the file type.
- Now, go back to excel and install this new add-in
- That is all. Now you can use getComment() formula in your workbooks.
Related: How to change the shape of cell comments from rectangle to any other shape
7 Responses to “Build models & dashboards faster with Watch Window”
yes, I use watches in excel vb
Dear Chandoo,
This is a quite useful for myself. I highly appreciate your efforts for acquainting us with this value adding and time saving tip.
I'll admit it, I never saw this feature before. I was familiar with using Watch Window in VB, but not in workbook. I'll definitely use this in future when building dashboards!
it was always great reading your blog, one thing that i want to know, which tool you are using to create such gif image?
it was also a nice post,,,,,, sir
Hello Sir,
Really very nice post. I didn't use this features in past. But now going to use it..
Thanks a lot for every time come up with something new..
Thanks
Very Nice post sir.
Every Time posting something new.. Thanks a lot