Game localization macros: export comment data to an adjacent cell

A quick and dirty method for exporting comment data

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
  • *

Screen capture two

- 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.

Screen capture three

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


Alain Dellepiane

Alain Dellepiane @gloc247 2 October 2011
Alain is the founder of team GLOC. Want to read more about localization? You should probably try this blog's Best of, which has a dozen of the best articles ready to read. (View all posts by Alain ➜)

Stay updated.

Receive a monthly email with the best game localization news, papers and tools.

Check out the latest issue. Free of charge, zero spam, unsubscribe any time.