Sign in Subscribe
3 min read

Useful Excel Macros for translators

Three macros to handle common tasks in game localization: adding line breaks and exporting cell comments

Automatically add line breaks

“Please add line breaks into your text. Each cell should be 28 characters per 7 lines max”

Likely cause: Japanese games often have a partial internationalization, where western fonts are in, but no word-wrap code is in place. So it's up to translators to put hard returns directly into the text. Factor in the time needed to manually add the line breaks, to fix them at each update, and the loss of productivity when using translation tools, and you have probably one of the most disruptive requests in our job!

Solution:

  • Paste the cells to be word-wrapped into column A
The text you want to word-wrap

Press ALT-F11 and paste the macro below

Sub Test()
    Const WrapAt As Integer = ***XX ***
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim i As Integer
    Dim Temp As String
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A:A")
    For Each Cell In Rng
        i = 0
        With Cell
            If Len(.Value) > WrapAt Then
                Temp = .Value
                Do
                    i = i + WrapAt
                    Do
                        If Mid(Temp, i, 1) = " " Then
                            Temp = Left(Temp, i - 1) & Chr(10) & Right(Temp, Len(Temp) - i)
                            Exit Do
                        Else
                            i = i - 1
                        End If
                    Loop
                Loop While i < Len(Temp) - WrapAt
                .Value = Temp
            End If
        End With
    Next Cell
End Sub

The macro being added
  • Replace ***XX *** on the second line with the word wrap value you need
  • Click on “Run” (the little green arrow on the top bar).
  • Done! Remember to use the time you saved for a good cause (like a final proofread, or go for a walk)!
Our text, beautifully wordwrapped

A couple of notes before you go:

  • This macro doesn't take font width into consideration: five thin characters lllll and five large ones MMMMM will be considered equal. If you really need to differentiate them, you are probably better off with the screen calipers
  • The macro aims at minimum length, not minimum raggedness, which may not always be visually perfect (i.e. it could leave a short word alone on the last line)
  • It doesn't seem to support lengths underneath 10 characters.

If you need something more advanced, download our custom macro here (instructions are inside)

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

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