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 > Data > Automatic Data Conversion). 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.
You can change the following automatic data conversion options in Excel for Windows and Excel for Mac:
-
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.
-
Converting a continuous string of letters and numbers to a date.
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.
Convert "date-like" combinations of continuous letters and numbers into a date
Disabling this option will store "date-like" values with continuous letters and numbers as text instead of converting to a date. For example, JAN1 remains as JAN1 in text, and isn’t converted to the date January 1. However, values that include a space or other characters, such as JAN 1 or JAN-1, may still be treated as dates.
See Also
Available number formats in Excel
