Forum Discussion
Extracting content from all cell notes on Excel sheet
I have used an Excel Sheet to record blood pressure readings for my health, and I have put a daily journal remark in a separate cell note for each day. I have hundreds of these notes, and I am trying to find an automated way to extract the content in each note (in order) so I do not have to open, copy, and paste each note's content individually. Can this be done?
- For anyone still trying to solve this:
UDF (user defined function) is a way to make a VBA script work like any other Excel formula
so as pointed out below
'-----------------------------------------------
Function CellNote(cell As Range) As String
CellNote = cell.Comment.Text
End Function
'---------------------------------------
creates a new function in Excel you can use to get the value of the "note"
To implement just copy the little snippet of code above, then (in excel) hit alt-F11 to bring up the macro/VB window, click Insert, Module paste the text, close the window and you can now type in =cellnote(A12) or whatever cell you're interested in and have the text waiting for you
There's no error trapping so if there's no note/comment you get the VALUE! error
When you save your excel file you will need to "save as" .xlms" otherwise you'll have to reenter the function every time!!! (this is true for the other VBA code examples)
UDFs don't update on all excel updates - but ctrl-alt-F9 does the trick! You can use the example custom function found at https://professor-excel.com/excel-extract-comments-text-to-cell/.
Function ReturnNoteText(cell As Range) ReturnNoteText = cell.Comment.Text End Functionuse as:
- Hi Chris, this function worked fine for me but when note changes my formula does not refreshes, do you know why?
Thanks in advanceEloyMendoza- it looks like https://professor-excel.com/excel-extract-comments-text-to-cell/ was updated for threaded comments. I have this version of Excel now so I can't test on an older version. My guess however is that you'll need to refresh the formula manually (enter edit mode and then enter; or similar manually) or I suppose you could do some VBA on cell change to refresh the calculation.
Thanks, Chris, I see that code.
I am a novice. I do not know how to create a macro, how to record it, save it, debug it, and then run it to do what it is supposed to do. Maybe this is just beyond me.
But thanks,
Noel
Learning more about Excel is not beyond anyone. https://support.office.com/en-us/article/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f provides you a good start.
