Important: This feature is currently available only on the Microsoft 365 Insiders Beta Channel. For more information and to become an Insider, join the Office Insider Program. The Excel product team encourages you to provide feedback. Select Help Feedback, select one of the Feedback options, enter #automaticdataconversions in the text box along with your comments, and then select Submit.

Excel automatically converts data when you add data to a workbook, such as opening a .csv file or pasting data into a range of cells. However, you can change these automatic data conversions to suit your own preferences by using Excel Options (File > Options > Advanced > Automatic Data Conversions). For example, when you add numerical data with leading zeros, such as 00123, by default Excel converts it to the number 123. But you may want to change this default behavior to keep the data as text and preserve the leading zeros.  

Currently, you can change the following automatic data conversion options in Excel for Windows:

  • Removing leading zeros from numerical text and converting to a number.

  • Truncating numerical data to 15 digits of precision and converting to a number displayed in scientific notation.

  • Converting numerical data surrounding the letter "E" to scientific notation.

Understanding automatic data conversions

Automatic data conversions can occur in the following ways: opening a .csv or .txt file, data entry or typing, copy and paste operations from external sources, find and replace operations, and using the Convert to Text to Columns Wizard. Let’s examine each automatic data conversion option in more detail.

Note: These automatic data conversion options don’t directly affect data imported by using Power Query, which has other ways to specify a data format. For more information, see Power Query for Excel Help.

Remove leading zeros and convert to number

Disabling this option will store numerical data with leading zeros as text and retain the leading zeros instead of converting it to a number and losing the leading zeros. For example, 00123 remains as 00123 in text, and isn’t converted to the number 123.  

Note: You may not be able to use numerical data saved as text in mathematical operations. If you want to keep the leading zeros while still converting to a number, use a custom cell format. For more information, see Keeping leading zeros and large numbers.

Keep the first 15 digits of long numbers and display in scientific notation if required

Disabling this option will store numerical data with 16 or more digits as text and retain all digits instead of only keeping the first 15 digits and converting it to a number that may be displayed in scientific notation. For example, 12345678901234567890 remains as 12345678901234567890 in text, and isn’t converted to the number 12345678901234500000 or displayed in scientific notation as 1.23457E+19. A common concern is a credit card number which often has 16 digits of numerical text that you want to keep without truncation. For more information, see Display numbers as credit card numbers.

Note: You may not be able to use numerical data saved as text in mathematical operations. Furthermore, conversion to a numerical format (such as Currency) always truncates the data to 15 significant digits of precision.

Convert digits surrounding the letter "E" into a number in scientific notation

Disabling this option will store numerical data surrounding the letter “E” as text instead of converting it to a number in scientific notation. For example, 123E5 remains as 123E5 in text, and isn’t converted to the number 1.23E+07 displayed in scientific notation.

Note: You may not be able use numerical data saved as text in mathematical operations. For more information, see Display numbers in scientific (exponential) notation.

See Also

Advanced options

Available number formats in Excel

Format numbers

Convert numbers stored as text to numbers

Import or export text (.txt or .csv) files

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×