How to Prevent Microsoft Excel Automatic Data Conversion

Microsoft Excel finally did something about loading CSV files that break Forwarder file numbers. All clients using Excel to load a data file should use this feature! MS-Excel is leveraged by Q-Law/Q-LawE, Collection-Master, and vExchange using Native vExchange, QvX, and CMvX, as well as other proprietary DTPs.

The Problem with Automatic Data Conversion

As we’ve mentioned in emails and vConnect articles, automatic conversion of numerical data in Excel can cause problems when you upload that data to vExchange. If Excel creates format variations, those variations create a mismatch between your data and that of your Forwarder/Sender. For example:

• Excel gets rid of the leading zeros in a Creditor FileNo.

• Excel truncates data, changing 1234-5678-9012-3456 to 1234-5678-90, for example.

New Automatic Data Conversion Settings

You now have the ability to change Excel’s default behavior and disable specific types of automatic data conversions.

• To do so, select File > Options > Data > Automatic Data Conversion.

• Under the Automatic Data Conversion section, choose the conversion(s) that you’d like to disable. Vertican recommends that our clients de-select the following Excel default settings.

  • Uncheck the box that says “Remove leading zeros and convert to a number.”

    • This means a number like 0000245686 would not be changed to 245686.

  • Uncheck the box that says “Keep first 15 digits of long numbers and display in scientific notation.”

    • This means a 16-digit credit card number would not be cut off.

  • Uncheck the box that says “Convert digits surrounding the letter “E” to a number in scientific notation.”

These features are available in the most recent versions of Microsoft Excel.

If you would like more information, please refer to Microsoft’s October 2023 blog: Control data conversions in Excel for Windows and Mac.