Home > Microsoft Excel, Microsoft Word, SDL Trados, Uncategorized > Translating Excel files in Trados Studio with embedded tags (html, xml, you name it)

Translating Excel files in Trados Studio with embedded tags (html, xml, you name it)

An excellent article here: Handling taggy Excel files in Studio… where this rule is added for the xls or xlsx filetype:

Start tag <[a-z][a-z0-9]*[^<>]*>

End tag </[a-z][a-z0-9]*[^<>]*>

My method would involve pasting in Word and using Trados styles and find/replace operations with regexes and then pasting back into Excel once translated. This method works fine, however, in case there are soft breaks in Excel files, when pasting back to Excel they are converted into paragraphs, hence new cells, creating problems when you have source and target columns and cells.

A way to resolve this is to replace the manual line break in Word with a string of characters that does not occur in the text, for example:

Find/Replace in Word

Find ^l

Replace xwg

— Translate, clean up and then paste to Excel.

— Then, select the range of translation cells.

— Press Alt-F11 to get into the Visual Basic editor

— Press Ctrl-G to get to the immediate window

— Paste the following: selection.replace "xwg", chr(10)

— Hit Enter to run it; soft breaks are restored without breaking cell boundaries (applies to Excel 2010 or higher).

Alternatively, run this macro (change the commas in the macro to semicolons in case your locale is Greek).

Sub Replacewithlinebreaks()
For Each c In ActiveCell.CurrentRegion.Cells
c.Value = Application.WorksheetFunction.Substitute(c, "xwg", Chr(10))
End Sub

Another option is to download ASAP Utilities

Go to Range » Find and/or replace in all sheets…
and enter xwg to search for, and {lf} for the replacement

  1. August 14, 2014 at 11:11 am

    If you need the SDL Setting files for Excel, I’ve uploaded the ones we use to my blog. Might save you some time:

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: