Game localization macros: auto-insert hard line returns

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

- 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

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

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