Excel has many problems and limits, but one function almost redeems it: VLOOKUP. This week we’ll see a couple of cases where it can really save your localization day. Realigning translations You are two weeks into the project and a good deal of translation has already been done, when the client writes you with an updated source. "We just edited a couple of lines. Please copy your translations and continue on the new file"
Then you open the file and you find out that the order of each single line has been changed. "Copying your translations" now means losing a couple of hours finding and pasting each single line, eyes wide open to be sure not to make mistakes.
Let’s use Vlookup! Take your new misaligned source and paste your old source and translation next to it, just like that Then go on cell B1, select Insert/Function and pick VLOOKUP in the list The settings are pretty self explanatory: the "Look up value" is the new source at A1, the table array is the old source/translation combo at C1/D8 , the column index number is the translation at 2 and the range lookup setting is stricly false or Excel will try to fuzzy match the results (and it’s terrible at it)
Very important : after setting the table array, remember to press F4 to turn the values into absolutes ($C$1:$D$8). In other words, if you don’t do it and then drag and drop the function, the values of your table will slide to D1/F8, D2/G9 and the whole thing will stop working correctly. The first result will appear: check that everything went fine, then you just need to select the little square in the bottom right corner of the cell and drag it down until the new source ends.
Here you go, all done! Just a couple of notes.
- The formula with these settings is VERY strict and will give you results only when old and new source are identical. That's good, as it guarantees that Excel didn't do any odd choice under your nose, but remember to investigate the lines that didn't appear as they might differ by very trivial details like spaces or line breaks. - Empty and non matching lines will appear as #N/A, which you will have to filter out and delete - When you finished realigning, remember to copy your cells and paste them as value where needed!
While preparing your translation memories, you might find out that source and translation are not only on separate files, but completely misaligned. This could be a serious showstopper, but before giving up, look for string ID in the columns around. For practical reasons, programmers often "tag" each string in their products with a unique alphanumeric code. If your project has something of the sort, you are saved! Give a quick look to ensure that source and translation really match, then proceed just like we did before. Only difference: our "Look up value" is now the string id. Enjoy you new TM! Pseudo translation with VLOOKUP
The dream of every game translator is to be involved in an epic RPG. The dialogues! The characters! The drama! Shivering with emotion, they open the first translation batch and… The Dreaded List of Weapons! On one hand, no RPG would be complete without hundreds of different weapons, on the other programmers can’t really spend their time programming and testing hundreds of truly different weapons. So they create a kind of weapon (the hammer), give it three levels of strength (lame, average, good), maybe stick some elemental value (Water Sword! Fire Sword! Lightning Sword!) and generate all the possible combinations. More often than not, this is reflected directly in the naming of the weapons themselves, under a thin layer of epicness and Japlish.
Fair enough but, as a translator, your focus is giving a clear and consistent version of the text, not spending hours copying and pasting the same words hundreds of times And most often than not, this kind of texts fools professional translation packages too. What can you do?
Once again, VALIGN can help. First of all, find out which are the base elements of your list and work out a good modular translation for each Then copy your source text and paste it as unformatted text inside Word. If your blocks are made by a single word, like above, simply replace all spaces with "tab character" (you can find it in the "Special" menu at the bottom) Otherwise, you need to replace each block with itself + tab character, so that "Emperor’s" is replaced by "Emperor’s^t". Just as simple, but a bit longer.
Now select everything, copy it and paste it back into Excel. Each block is now on a separate column, so you can use VLOOKUP to match each one with its translation. You can also swap columns in the translation in order to match its structure (in our case Soldier’s/General’s/Emperor’s comes at the end) Last step, collate your translations using CONCATENATE and adding a space between each block Copy the results, paste them as value and you are done! With all the time you saved, please try and find some slightly less contrived names!