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
8 Responses to “What is LAMBDA? 4 Practical examples to REALLY understand it”
Thanks so much for this, it's utterly brilliant!
Silly question - I assume LAMDA will work with dynamic arrays?
Very much so. Many of the new functions like MAP only make sense in the context of dynamic arrays and Lambda functions.
As usual, very informative material. Easy to understand and apply!
Thanks for making everyone awesome!
Easy to understand Lambda function through this tutorial. Thanks Chandoo.
I have Officce 365 (updated), but I can't see LAMBDA function. 🙁
I dont see "Office Insider" option in my excel 365.
Another option for First Monday...
=LAMBDA(anydate,WORKDAY.INTL(EOMONTH(anydate,-1),1,"0111111"))
Loving the binary options in WORKDAY.INTL David...