End client: Various HR end clients needing Benefit Portals for their employees

Project Description: Translating a proprietary portal into Spanish

Challenge: The client works in a proprietary content management system using Excel as the source file repository of all content. Strings often are out of context, containing variable data and do not represent a cohesive picture of the end result.

Sample of the Excel file shows the complexity of the Excel file by tracing cell dependencies that are referenced.

The scope

There are many different ways in which content management systems handle the export of content. Some export into Excel; others have capabilities to export to XML. The latest trend in software development is to adhere to XLIFF standards for multilingual support.

This case study is about a portal that was written and created using Excel as a back-end to the portal’s content. Most of the logic and inner workings of the portal was directly programmed in Excel with no clear way to export into a more process-friendly format like XML. The Excel file was set up to where the translation had to be placed adjacent to each cell.

The problem with Bilingual Setup

The problem with a bilingual setup is that often clients think we can pick up copy from one place and put it somewhere else automatically. Of course, this assumption is based on thinking that a translator is working directly into this Excel file (which excludes them from using any industry tools such as spell check, terminology or translation management tools).

However, any Computer Assisted Translation (CAT) tool out there using Translation Memory and Termbase technology relies on having content that it can process and replace at the same time. So we have to figure out the process for dealing with this.  Our solution: we had to copy over English copy into the adjacent cells that were going to be translated. With Excel, you have to be careful to take into account any cell references that might be affected. That meant going through the copy to ensure that any references to other cells were correct. Some references had to stay absolute as it referenced a name that would not change or a date that was hard coded into a sheet with nothing but data. Other cells referenced other paragraphs that would be called up and had to be translated as well. Ay yay yay…are you getting a headache reading this??!

Localizing Excel Data

One interesting challenge in converting Excel data to Spanish was dealing with auto-generated calendar dates. Because some of the datafields generated from a simple date entry (such as 9/15/2016) and then were called up in another cell using a written out format (such as September 15, 2016), these cells could not be simply translated. For this, we had to resort to a table of localization codes that can be added in to convert a date into Spanish. By adding the code [$-0C0A] before the date format (mm, dd, yyyy), we were able to prepare every date in the Excel file with the Spanish output by Excel.  Keep in mind, translators don’t do this.  Their focus is on writing. This is a Localization Project Management task.

Processing and Testing content for Translation

While every effort is being made to add information that helps with context, a large Excel file with tons of data that does not need to be translated is not helpful information. However, in order for Excel to function properly, you  simply cannoty extract content and place it in a clean document when you have functions relying on data coming from other cells in the same document. Luckily, SDL Studio (a technology in our industry that we use) had a feature that allowed us to format text in a particular color and let that particular text be processed. This helped us work in the same document as the final output, avoiding additional work after translation which saved the client costs. The goal of the preparation process is always to anticipate any processing issues that we can fix before translation, rather than having to correct these in the translation process.

Translating Conditional Statements in Excel

Translating Conditional statements is always a challenge. One issue with Excel formulas is that when you process the content, you process the output, not the formula. This is also an issue in Word. Therefore any formula that represents more information than its output (like we have with conditional statements), requires a workaround; that is to turn a formula into text (by adding a period before the = sign). That ensures processing of all information in a cell instead of the formula’s output.

Providing Context for Grammar

Here’s one simple issue that illustrates the problem with context. The name of the program ([Company] Pension Plan Lump-Sum Opportunity) sometimes was used as a proper name of the program (or center handling the administration), and sometimes used as a description of the opportunity.

For anything descriptive, we decided earlier in other materials, we would translate the name (since it was about a pension opportunity). However, in English, the cell referenced for this name was always the same.

This required our preflight process in project management to change references of descriptive text to a cell adjacent with the translated name. Context was required to make sure that the translator knew that the name would be returned in Spanish and the translation had to meet any grammatical requirements in the text. For instance “Pension Plan Lump-Sum Opportunity” Information where the information between quotation marks is called from another cell (=ResultElection!$B117&” Information“) requires  knowledge of what the reference means and also requires in Spanish to alter the order of the code because “Plan Information” in Spanish is reversed as “Information (of) Plan”. Here is a link describing how we deal with context and grammar in conditional statements.

Providing Context for Meaning

The other issue with context is that working with individual Excel strings does not give you a good idea of the overal picture of what it will look like. Plus, the Excel file was not the only file that had information. We had different resource strings as well that referenced several actions relating to specific topics. Sometimes, seemingly generic phrases like action statements can relate to just about anything in English. In other languages, the grammatical term for a particular action can be different depending on context. It’s important to provide specific context.

Sometimes strings that go together (like a list of options) do not process all together due to the way it is coded or organized. In this Excel file, we had to sometimes reference individual strings along with the other options to ensure proper parallelism between each item and some consistency in punctuation and grammatical structure.

Final Stages – Browser Testing

After completing the Excel File and making sure that all formulas were restored to work, the client loaded the file into their system for Browser Testing on the staging server. Browser testing had to be done several times to test different paths through the portal. Screenshots were provided to the client with directions. Ultimately, given the complexity of the site, we had only 10 comments on the entire site. Without any preflight or preparation, the implementation process would have been a much more difficult process.