Displaying Character Count in Excel per Cell
On many displays, the amount of characters that can be shown is very limited. Especially mobile gaming devices (Nintendo DS, PSP or simply a mobile phone) create different challenges for translators and editors.
The sentence “Kill ten boars with your bare hands then come back to me and I give you a shiny sword” is no problem for an MMO displayed on a computer monitor. But with 85 characters, it might simply be too long for an iPod.
Very often, translators will receive Excel files which were formatted by the client in a way that they will not allow any more characters than acceptable. I personally do not like this method, as I prefer to translate first and then to edit and/or truncate.
With Excel’s LEN function, displaying the character count per cell is very easy, even for people who never created a formula and have the formula bar disabled by default (if this is the case for you, go to VIEW and check Formula Bar).
Let’s say you have an Excel file with three columns. In column A, the client wrote notes and mentions also the character limit for this particular row. Column B contains the original, column C is where you put in your translation.
Something like this:
We know the character limit is 90, but how long is the translation in column 3? Let’s ask Excel. Click the cell to the right of the translation, in our example the adress of this cell is D3. See that little fx button to the left of the formula bar? Click it. See that field where Excel asks you for a brief descriptionof what you want to do? Let’s write “character count” in here. In the list below, LEN is our top result, it “Returns the number of characters in a text string”. Exactly what we wanted! We select it and click OK.
Brr, now we are supposed to enter “Function Arguments”, looks all very complicated. But Excel only wants to know in which of the cells we want this function applied to. Well, cell C3 of course, our translation. We can type C3 in the field “Text” or simply click cell C3 in our work book. And voilà, the number 93 appears in cell D3, our text is three chars too long! We can edit cell C3 now, as soon as we leave the cell, the character count is updated in D4. That’s it.
But wait, there is more. When you select our function cell D3, you will see a little black square in its bottom right corner. You can click and drag this all the way down your rows and Excel will automatically duplicate the function until you release.
Thanks for this tip, beautifully explained and very helpful. Manuals and software of all sorts have character limits and, thanks to your advice, I can do it standing on my head!
Cheers,
Leah
German to English translator
This has saved me so much time. I won’t embarass myself by explaning my old method.Thanks for taking the time to write this up.