Client quote: “Please translate cells from 1 to 99834. Take into account the hundreds of randomly placed and jumbled Excel comments. Kindly deliver asap. ”
Likely cause : maybe they just love the Excel comment function, or they chose to export the edits like that. No matter the reason, browsing each single cell for the elusive little red triangle (not to mention fishing out its yellow comment hundreds of lines below) can be very time consuming, especially if you use a TM tool for the actual translation.
- Select the column with the comments
- Press ALT-F11 and paste the macro below
Sub CopyCommentText() Dim CmtText As String Dim CmtCount As Integer, i As Integer Dim CommCell As String
CmtCount = ActiveSheet.Comments.Count For i = 1 To CmtCount CommCell = ActiveSheet.Comments(i).Parent.Address Range(CommCell).Offset(0, 1) = ActiveSheet.Comments(i).Text Next i
- Click on "Run" (the little green arrow on the top bar). - Done! The content of each comment is now transposed into the next column, ready to be filtered or imported into your TM tool.
Macro by Dave Hawley, published on the always precious Mr Excel website… more than ten years ago! (Hey, it doesn’t make it any less useful!)