I’ve been playing a lot with Numbers lately. I think Apple have really created something great here - whenever I use Excel I always spend hours trying to make the spreadsheet look good. Things like headings, graphics, borders etc. take ages to get right - resizing rows, merging cells, and so on. With numbers it is such a pleasure to make spreadsheets that actually look great with little effort.

Anyway, I was interested in comparing functions in Excel to functions in Numbers. There are a lot more functions in Excel than Numbers, though most of the basics are in Numbers. I was thinking of writing a blog outlining the differences, but I got side tracked when I started looking at the Excel functions CODE and CHAR.

Excel tells me that CODE:

Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.

Numbers tells me:

The CODE function returns the Unicode code number of the first character in a specified string.

Ok - Numbers mentions Unicode. I figured I’d try some experiments with CODE and CHAR - using both Numbers and Excel.

I started Numbers and typed in a cell =CODE("A"). It returned 65. Not unexpected. I tried this in Excel. Same result.

So next I tried some Japanese. I typed in a Numbers cell =CODE("か"). That’s the hiragana character KA for those who can’t make it out (first character of my wife’s name for those wondering why I chose it). The result in Numbers was 12363 (304B in hex). This is the correct Unicode value.

I tried this in Excel. I got the result 95. Clearly an odd return value - but in Microsoft’s defence, they never promised CODE would work for double byte character sets. So I tried the reverse of CODE, that is =CHAR(95). It returned an “_” character.

I remembered though that Excel doesn’t work very well in Japanese, by default, using English Excel. You need to convert your application to use Japanese. Absolutely bizarre way to do things - most Applications on the Mac work with any language - no need to change the application in some way. Anyway, I followed the instructions to change to Japanese Excel. Some of the menus and dialogs are now in Japanese, but I can still do what I need for testing. So I test again:

=CODE("か") now returns 9259. That’s not Unicode either! I spent a lot of time trying to work out what that code represents. It turns out it is ISO-2022-JP.

So here are the results of my testing for each of these applications.

Results from Numbers

Results from English Excel

Results from Japanese Excel

Numbers stores its files as XML, and you can see the use of Unicode in the text:

...
<sf:formula-string sfa:string="=CODE(&quot;&#x304B;&quot;)"/>
...

So what does all this prove? Nothing really. Well, except that Numbers makes use of the more modern encoding format of Unicode more than Excel. The differences in CODE and CHAR could make for some obscure errors when importing Excel documents to Numbers documents, though I’m not sure these functions are generally used that much anyway. It would be interesting to hear how Japanese users of Numbers are finding the compatibility between files in Excel and files in Numbers.