Game localization macros: auto-insert hard line returns

A quick and dirty method for automatically adding hard line returns in Excel

Client quote: “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](http://localization.it/2011/08/game-localization-tools-5-tiny-and-free-apps-that-will-save-your-day.html)
- The macro aims at [minimum length, not minimum raggedness](http://en.wikipedia.org/wiki/Word_wrap#Minimum_length), 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 below 10 characters.

Macro by Andrew Poulsom, published on the legendary Mr Excel website… almost ten years ago! (Hey, it’s still one of the most useful macros around!)


Alain Dellepiane

Alain Dellepiane @gloc247 01 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.