MS Excel query

[captainmorganrum]

I've got a spreadsheet, with a few tabs, one of them has about 100 cells containing comments (lists, names etc).
Is there any way I can quickly export these out of the comments into a seperate sheet, or do I have to labouriously go in and copy and paste them out one by one?

Cheers

 

[Acker79]

You can highlight all the cells and copy and paste them all in one go.

 

[Gazwag]

Not aware you can do much if they are comments

 

[Acker79]

or save the file under another name, then delete the unwanted tabs.

 

[clapham_gull]

Yes very easily, but you'll need to know a bit of VBA.

I can probably knock you up a function to do it later this evening.

 

[Robbie G]

Vlookup function?

Hard to tell if it'll work for what you have.

 

[mcshane in the 79th]

I'm not aware of any way of copying the just text from the comment boxes, other than as Clapham Gull says by using VBA. But I'm not clever enough for that

 

[clapham_gull]

I've already worked it out, I'll post it a minute

 

[Acker79]

I've already worked it out, I'll post it a minute

Did it take longer than saving it as a second file then deleting unwanted tabs?

 

[clapham_gull]

You need to create a module (if you know how to do that) and paste the following code.


Function ExtractComment(s As Range) As String
ExtractComment = Replace(s.Comment.Text, s.Comment.Author & ":", "", 1, 1)
End Function


To use it, just use it as a regular function referring to the cell where the comment is


For example if the comment is "over" cell A1, write in an empty cell


=ExtractComment(A1)



The way I've written it takes out the name of the author of the comment which is placed at the front. If you want to keep the author, do this instead


Function ExtractComment(s As Range) As String
ExtractComment = s.Comment.Text
End Function



If there isn't a comment the function will return an error (#Value), if you want it to simply return nothing, I'll fix it for you.

 

[clapham_gull]

Did it take longer than saving it as a second file then deleting unwanted tabs?

If you want to extract the comments into a sheet, then yes.

 

[captainmorganrum]

You need to create a module (if you know how to do that) and paste the following code.

Function ExtractComment(s As Range) As String
ExtractComment = Replace(s.Comment.Text, s.Comment.Author & ":", "", 1, 1)
End Function


To use it, just use it as a regular function referring to the cell where the comment is


For example if the comment is "over" cell A1, write in an empty cell

If there isn't a comment the function will return an error (#Value), if you want it to simply return nothing, I'll fix it for you.



=ExtractComment(A1)


The way I've written it takes out the name of the author of the comment which is placed at the front. If you want to keep the author, do this instead


Function ExtractComment(s As Range) As String
ExtractComment = s.Comment.Text
End Function

Brilliant stuff - thanks for that - I'll give it a go!

 

[clapham_gull]

This function won't return an error if the caption is blank


Function ExtractComment(s As Range) As String

If Not s.Comment Is Nothing Then
ExtractComment = Replace(s.Comment.Text, s.Comment.Author & ":", "", 1, 1)
Else
ExtractComment = ""
End If

End Function

 

[captainmorganrum]

Superb, got the data from that - I'll finish it off tomorrow.
Cheers Clapham - you're a genius!

 

[mcshane in the 79th]

Did it take longer than saving it as a second file then deleting unwanted tabs?

I think you missed the point of what he wanted to achieve