Excel automatically converts text values even when I want to keep the original format

Question

I’m using Excel for Microsoft 365 and regularly work with CSV files containing IDs and reference numbers.

When I open the CSV file in Excel, some values are automatically converted. For example:

• 001234 becomes 1234
• 1-2 may be interpreted as a date
• Long numeric IDs may be displayed in scientific notation

I understand that I can import the file through Data > From Text/CSV and manually specify the data type, but I’m looking for a way to control this behavior when simply opening CSV files directly.

Is there a setting that allows me to permanently define how Excel should interpret certain columns or patterns when opening CSV files?

Ideally, Excel could remember my preferred import behavior or provide more granular control over automatic data conversion.

Answer 1

Hello GeekThe-4278,

Excel does not currently provide a setting that permanently defines how specific columns or custom patterns should be interpreted whenever a CSV file is opened directly. CSV files do not store column data types or import rules, so Excel uses its default data conversion behavior when opening them.

When Excel opens or loads text-based data, including CSV files, it may automatically interpret certain values and convert them into another format. For example, Excel may remove leading zeros, shorten long numbers after the first 15 digits, interpret values containing the letter “E” as scientific notation, or convert a combination of letters and numbers into a date.

To help prevent these unwanted changes, please open Excel and go to: File > Options > Data > Automatic Data Conversion

Under Automatic Data Conversion, untick "Enable all default data conversions below when entering, pasting, or loading text into Excel". Alternatively, you can leave the main option enabled and clear only the individual conversions that are affecting your data.

These settings are intended to give you more control over how Excel handles values that should remain exactly as they appear in the source file, such as account numbers, product codes, tracking numbers, or other text-based identifiers.

You may also keep "When loading a .csv file or similar file, notify me of any automatic data conversions" selected. This allows Excel to notify you when a CSV file contains data that may be converted automatically.

Please note: This feature is available in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac.

For your reference: Set automatic data conversions | Microsoft Support

I hope these settings will help Excel handle your data more accurately and prevent unexpected format changes. If there is anything else you would like to share or if you need any further assistance, please feel free to update me, and I will be happy to help.

Kind regards,

---

If the answer is helpful, please click "Yes". If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in documentation to enable e-mail notifications if you want to receive the related email notification for this thread.