Thursday, January 06, 2011

Excel gotcha

Just got bit by a... I can't call it "bug", but at least surprising behavior, in Excel 2007 (though I believe it is the same even in other versions).

I had a column of text (file names) in a csv document which was opened in Excel and re-saved as a regular Excel files. The recipient of that document asked me while some of the file names were in scientific notation :) It puzzled me for a bit, until I realized that a lot of the file names had the form 12E12345. When the part after the "E" was small enough (I didn't bother searching for the limit, but I'd make a wild guess and say below 300), Excel would interpret that string as a number in scientific notation - so 01E00212 became 1E+212.

The program I used to generate these csv documents knew to prefix strings made up of only digits and starting with zeroes with an apostrophe - like '00123. This one, however, caught me unprepared :)

No comments: