A collection of computer systems and programming tips that you may find useful.
 
Brought to you by Craic Computing LLC, a bioinformatics consulting company.

Monday, August 27, 2012

Preventing Excel from wrongly interpreting Strings in CSV files as Exponential numbers

MS Excel and Apple Numbers both attempt to guess the format of the contents of each cell when they open up a CSV file. Most of the time this is fine but in some cases it causes a big problem.

If you have a string with numbers and the letter E then both programs will treat this as a number in exponential notation.

For example, the string 229E10 will be converted to 2.29E+12

What makes it worse is that you cannot change it back to the original text using the cell format options.

I run into this problem with strings that represent microtiter plate and well assignments. For example, 229E10 refers to plate 229 row E column 10. 

I just want the spreadsheet program to take what I give it as text. Putting the string in single or double quotes can work but then the value remains in quotes in the spreadsheet.

The best way to do this seems to be to use double quotes around the string AND prefix that with an equals sign - like this:
="229E10"

For reasons that I do not understand, Excel will strip the extra formatting off the string and treat the contents as plain text. This also works for arbitrary strings, such as ="ABC"



1 comment:

Denys Slipetskyy said...

Or you can put single quote before string, like: '229E10

Also hate when office tools try to be smarter than you.

Archive of Tips