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!
- 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
- 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.
- Select the column with the comments
- 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
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.