Excel 365 and Excel 2024 have better text conversion options for importing text files with more conversion options. Automatic Data Conversion is now available in Excel for Windows and Mac.
Opening .csv files directly in Excel is done a lot. Microsoft encourages it by associating .csv so those files are opened directly in Excel by default. That’s quick and easy, really too easy because that kind of direct opening doesn’t always work very well.
Excel makes a lot of assumptions about the text in each cell, converting into various Excel data types without warning and not always getting it right. Genetic researchers have been caught too many times because the names of some genes are mistaken for dates or very large numbers.
Excel 365 and Excel 2024 have Automatic Data Conversion which allows more control when importing pasting or even typing into cells. More on that below.
All the Automatic Data Conversion options
Here’s all the Automatic Data Conversion options (File | Options | Data in Excel for Windows).
Enable all default data conversions below when entering, pasting, or loading text into Excel
Remove leading zeroes and convert to a number
A code like 00054 will be converted to the number 54.
Keep first 15 digits of long numbers and display in scientific notation
Maintains, as best is possible, the value of a long number by keeping 15 digits then adding an exponent.
Convert digits surround the letter “E” to a number in scientific notation
“1234E5” is read by Excel as a number with exponent 1234x105 (displayed as 1.23E+08 in Excel). Thanks to Martin B for correcting our math notation.
Convert continuous letters and numbers to a date
A code like MARCH1 is converted to 1 March in the current year.
When loading a .csv file or similar file, notify me of any automatic data conversions
The new Excel feature will NOT stop the genetic code conversion problems unless scientists change the options before importing data.
All the conversion options default ON which means that Excel will import text in the same way it has in the past.
IF you need to stop some of those behaviors (for example, a geneticist) then UNselect some of the conversion options.
For example, to stop text like “MARCH1” converting to a date, uncheck “Convert continuous letters and numbers to a date”.
A code like “1234E5” will still be converted to a large number 12345 unless the option “Convert digits surround the letter “E” to a number in scientific notation” is deselected.
The new options are a good move overall. It’s understandable that the ‘out of the box’ defaults reflect the long-standing text conversion behavior but that leaves the risk of conversion problems continuing.
Presumably that’s why there’s also a “…notify me of any automatic data conversions” feature that will warn anyone that their incoming text will be tampered with.
Not just importing .csv files
According to Microsoft, Automatic Data Conversion applies in many situations, not just importing .csv text data.
- Type directly into a cell.
- Copy and paste from external sources (e.g., a web page).
- Open a .csv or .txt file.
- Find and replace operations.
- Select Data > Text to Columns, and then use the Convert Text to Columns Wizard.
‘Number stored as text’ errors remain
You’ll still see ‘Number stored as text’ errors even though you’ve asked Excel to keep as text. Microsoft considers that as ‘as expected’ behavior (really?).
Not in macros
There are no VBA options to disable these conversion options.
Use better Excel conversion options
Microsoft and Excel have been taking a lot of blame for these text file conversion problems and, for once, we’re on Redmond’s side.
Excel already has text import options to prevent conversion errors.
The Text to Columns Wizard has been in Excel for a long, long time and lets you control the format/conversion of each column of incoming data.
Get & Transform (PowerQuery) in modern Excel has even more and better data import controls.
Automatic Data Conversion will, hopefully, stop people just opening text files into Excel without checking the import and conversion process properly.
Why Excel causes errors in gene studies and how to fix
Gene names changed to stop confusing Excel
Better text conversion options coming to Excel (the earlier version of Automatic Data Conversion).
