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
vpopperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vpopperAuthor Commented:
PERFECT!!!  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.