Extracting content from all cell notes on Excel sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1093195%22%20slang%3D%22en-US%22%3EExtracting%20content%20from%20all%20cell%20notes%20on%20Excel%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1093195%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20used%20an%20Excel%20Sheet%20to%20record%20blood%20pressure%20readings%20for%20my%20health%2C%20and%20I%20have%20put%20a%20daily%20journal%20remark%20in%20a%20separate%20cell%20note%20for%20each%20day.%20I%20have%20hundreds%20of%20these%20notes%2C%20and%20I%20am%20trying%20to%20find%20an%20automated%20way%20to%20extract%20the%20content%20in%20each%20note%20(in%20order)%20so%20I%20do%20not%20have%20to%20open%2C%20copy%2C%20and%20paste%20each%20note's%20content%20individually.%20Can%20this%20be%20done%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1093195%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1093238%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20content%20from%20all%20cell%20notes%20on%20Excel%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1093238%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F508319%22%20target%3D%22_blank%22%3E%40Noel_Sherry%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20example%20custom%20function%20found%20at%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fprofessor-excel.com%2Fexcel-extract-comments-text-to-cell%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fprofessor-excel.com%2Fexcel-extract-comments-text-to-cell%2F%3C%2FA%3E.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EFunction%20ReturnNoteText(cell%20As%20Range)%0A%20%20%20%20ReturnNoteText%20%3D%20cell.Comment.Text%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3Buse%20as%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20651px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F163948i79FCB018E33CD05B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%225.png%22%20title%3D%225.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1093464%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20content%20from%20all%20cell%20notes%20on%20Excel%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1093464%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303863%22%20target%3D%22_blank%22%3E%40ChrisMendoza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20Chris%2C%20I%20see%20that%20code.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20novice.%20I%20do%20not%20know%20how%20to%20create%20a%20macro%2C%20how%20to%20record%20it%2C%20save%20it%2C%20debug%20it%2C%20and%20then%20run%20it%20to%20do%20what%20it%20is%20supposed%20to%20do.%20Maybe%20this%20is%20just%20beyond%20me.%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20thanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3ENoel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1094800%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20content%20from%20all%20cell%20notes%20on%20Excel%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1094800%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F508319%22%20target%3D%22_blank%22%3E%40Noel_Sherry%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3ELearning%20more%20about%20Excel%20is%20not%20beyond%20anyone.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fcreate-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fcreate-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f%3C%2FA%3E%26nbsp%3Bprovides%20you%20a%20good%20start.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1094922%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20content%20from%20all%20cell%20notes%20on%20Excel%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1094922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303863%22%20target%3D%22_blank%22%3E%40ChrisMendoza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20Chris%2C%20I%20will%20start%20there.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENoel%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

7 Replies

@Noel_Sherry -

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 Function

 use as:

5.png

@ChrisMendoza 

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

@ChrisMendoza 

Thanks, Chris, I will start there. 

 

Noel

Hi Chris, this function worked fine for me but when note changes my formula does not refreshes, do you know why?
Thanks in advance

@EloyMendoza- 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.

Chris, Thanks for your quick answer.