Did you know that Excel could give you different calculated values based on what the default language for your PC is?

I’m working on a translation project … and the translation company gave us a excel spread sheet with a list of screen fields that need to be expanded.

This spread sheet uses the LENB function to calculate the length of the text that needs fit on the screen … and uses that calculated length to indicate how much the field needs to be expanded.

LENB is (supposed to be) aware of double byte & single byte character sets … unfortunately, it’s only aware of double byte characters when Japanese is the default language on your PC.

So, if Japanese IS the default language on your PC, and a character is double byte … it will return 2 as the length of the character. However, if Japanese is NOT the default language on your PC … it will return 1 as the length of a character.

There’s no warning about this potential discrepancy when you load the spread sheet … the only way I was able to find this out was to dig through the documentation.

As a result of this, I just spent a week and a half working on expanding fields … and it turns out that the size I expanded the fields to was incorrect … so those have to be redone … and the entries in the spread sheet that were less than 1 actually DO need to be expanded.

LENB is a totally brain dead and stupid function … there is NO situation where a calculation such as that should be effected by what the default language of the PC is.   It should return the same value regardless of what machine it’s run on.

Turns out there are a bunch of functions that behave this way … FINDB, MIDB, LEFTB, MIDB, REPLACEB, RIGHTB, and SEARCHB.

A friend suggested that OpenOffice might be better … but, at least as far as I can see, OO doesn’t have functionality that’s aware of double byte characters.