Need a Macro to extract embedded EXCEL comments

Posted on 2014-08-01
Last Modified: 2014-08-04
I have three colomns of EXCEL data - one I created as a unique line ID.  The next column contains the word VOID but there is a different embedded comment in each sell. I need to extract all of these comments to the next column so that they can be analyzed. Can anyone help?  Thanks

id      "X" Confirms Complete          Extract Comment to here
29      VOID       
32      VOID      
49      VOID       
51      VOID      
86      VOID       
88      VOID      
97      VOID      
98      VOID      
104      VOID      
107      VOID      
134      VOID      
137      VOID      
153      VOID      
155      VOID      
178      VOID      
179      VOID      
183      VOID      
193      VOID      
203      VOID      
222      VOID      
242      VOID      
244      VOID      
245      VOID      
246      VOID      
248      VOID      
255      VOID      
262      VOID
Question by:vpopper
    LVL 20

    Expert Comment

    by:Ejgil Hedegaard
    Try this function, insert in a VBA module.
    For C2 it will be =ExtractComment(B2)
    Copy down.
    Lines in the comment are divided by |
    If no comment the result is "No comment"

    Function ExtractComment(rg As Range) As String
        If rg.Comment Is Nothing Then
            ExtractComment = "No comment"
            ExtractComment = Replace(rg.Comment.Text, Chr(10), " | ")
        End If
    End Function

    Open in new window


    Author Comment

    I can's seem to get this to work. I am not very good with VBA.  
    Can you help - file attached???   I prob. then could adjust the range once the maco is in place.

    LVL 20

    Accepted Solution

    It is a function, and the use is equal to use any other formula.
    You don't have to adjust the range, since the function is used for one cell at a time.

    In C2, the function gets the comment from B2, or any other cell you use as argument.
    When the formula =ExtractComment(B2) is in C2, it can be copied to the other cells.
    See file.

    Author Closing Comment

    PERFECT!!!  Thank you!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
    It Is not possible to enable LLDP in vSwitch(at least is not supported by VMware), so in this article we will enable this, and also go trough how to enabled CDP and how to get this information in vSwitches and also in vDS.
    Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
    Video by: Zack
    Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now