Game localization macros: export comment data to an adjacent cell

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.

Solution:

- Select the column with the comments

Screen capture one

- 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

End Sub

- 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!)