[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

Need a Macro to extract embedded EXCEL comments

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
0
vpopper
Asked:
vpopper
  • 2
  • 2
1 Solution
 
Ejgil HedegaardCommented:
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
    Application.Volatile
    If rg.Comment Is Nothing Then
        ExtractComment = "No comment"
    Else
        ExtractComment = Replace(rg.Comment.Text, Chr(10), " | ")
    End If
End Function

Open in new window

0
 
vpopperAuthor Commented:
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.

THANKS!
comment-extract.xls
0
 
Ejgil HedegaardCommented:
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.
comment-extract.xls
0
 
vpopperAuthor Commented:
PERFECT!!!  Thank you!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now