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.

Numbers Unicode Test
Results from Numbers

English Excel Unicode Test
Results from English Excel

Japanese Excel Unicode Test
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.

iWork Numbers, Excel and Unicode

7 thoughts on “iWork Numbers, Excel and Unicode

  • November 16, 2007 at 4:39 am
    Permalink

    I think you just found out that excel doesn’t support unicode. For years I tried to make a list of chinese / dutch / german words, and could only do it in word, because excel couldn’t handle the chinese characters. I think this is just an annoying bug on microsoft’s side…

  • April 3, 2008 at 10:10 pm
    Permalink

    hi, i was just wondering whether you know if numbers has the pearsons coefficient like excel? And whether you thought it best that i use microsoft for mac instead as i will be using numbers for statistical purposes?

    thanks

  • April 3, 2008 at 10:30 pm
    Permalink

    Hey blondie. You might to read my other blog article on Numbers – http://curmi.com/blog/2007/08/26/functions-in-excel-and-numbers/

    In particular, take a look at the spreadsheet I mention. You’ll see that Pearsons Coefficient is not available in Numbers. So my answer to your question is, currently, you’ll most likely need to use Excel.

  • May 16, 2008 at 8:48 am
    Permalink

    Yo. I came here looking for a solution to this problem. In my search I found out that apparently there is something called “Power Utility Pak” that will let one do this in Excel. While PUP isn’t gratis, its existence makes me believe that there might be a competitor that is. Anyway, I think I’ll be checking out OpenOffice.org instead, because this is just too much of a hassle.

  • June 6, 2008 at 4:50 pm
    Permalink

    Hi

    If you think that numbers is so great, then why don’t they have this “broom” function where you can copy the stile of a box into another one? Espescially if the boxes have special lines around them.
    It would be really good if you woud know that.
    thank you!

  • June 6, 2008 at 5:01 pm
    Permalink

    It is there in the Format menu. Copy Style and Paste Style. ALT-CMD-C and ALT-CMD-V.

  • August 7, 2008 at 4:35 pm
    Permalink

    It seems obvious that they should upgrade their excel’s code() function. Microsoft should be able to do this as it is one of the most profitable company in the world.

    The internal char handling scheme of excel may need to be changed in order to achieve this. But it should not be a problem for their programmers which should be among the best in the world …

Leave a Reply

Your email address will not be published.

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image