Skip to content

Better text conversion options coming to Excel

Microsoft was addressing the long-standing problems with Excel 365 importing text files (.CSV) by adding some options to prevent common conversion problems. The ‘Automatic Data Conversion’ choices are welcome, but you’re still better using other, safer, Excel import options.

NOTE: this feature has been quietly dropped by Microsoft. The Automatic Data Conversion options do not now appear in Excel 365. We strongly recommend using the Text Import Wizard or PowerQuery (Get & Transform) to import text files (.csv).

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.

Now Microsoft are addressing the problem directly, even though there are already better solutions, see below.

Automatic Data Conversion

Excel 365 is getting some options to prevent common text conversion problems. Go to File | Options | Advanced | Automatic Data Conversion.

Source: Microsoft

When entering, pasting or loading text, Excel will perform these conversions by default

  • Remove leading zeros and convert to a number
  • Keep first 15 digits of long numbers and display in scientific notation
  • Convert digits surrounding the letter ‘E’ to a number in scientific notation.

When loading a .csv file or similar file, notify me of any automatic data conversions.

All these options default ON which is the equivalent of the way Excel already works. What’s changing is that users have the option to turn some of the automatic conversions OFF.

Geneticists could avoid some of the common gene code import problems by turning OFF the ‘Remove leading zeros …’ and ‘Convert digits surrounding letter E’ options.  However that won’t fix all the genetic conversion problems (e.g MARCH1 would still convert to a date).

Who gets it?

Automatic Data Conversion options are first in Excel 365 for Windows, Insiders Version v 2207 build 15427.20000 then later in preview and public versions.

Better choices for importing .CSV into Excel

These new options are nice to have but aren’t enough to solve the CSV to Excel problems.

Opening small .CSV text direct in Excel is fine because you can easily see the results.  But as the file size grows (more row and columns) it’s easy to miss automatic conversion errors.

It’s MUCH better to use Excel’s data import options to control the incoming text and how it’s transformed into cells.

There are two ways.  In modern Excel there’s ‘Get and Transform’ which has sophisticated conversion and transformation options. 

The Text Import Wizard (in Excel for Windows and Mac) lets you Older Excel’s have the Text Import Wizard which lets you specify the data type for each column, overriding Microsoft’s defaults.  In particular, choose Text or a date format.

Gene names changed to stop confusing Excel

Excel’s General problem that messes up what you type

Great text importing tricks for Excel

Import PDF’s into Excel and refresh later

Excel’s much better data import features

About this author

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.