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("か")"/> ...
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.
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…
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
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.
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.
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!
It is there in the Format menu. Copy Style and Paste Style. ALT-CMD-C and ALT-CMD-V.
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 …