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