Four settings that control whether Excel converts text to numbers and dates.
- On default
- All four conversions below are on, which is how Excel has always behaved.
- Off
- All four are off, and entries like 00123 and JAN1 stay as text.
When you type, paste, or open text, Excel decides whether each value is really a number or a date and converts it. That's usually what you want, but not for codes and identifiers. Four settings let you turn off the conversions that damage this kind of data:
| Setting | You enter | Setting on | Setting off |
|---|---|---|---|
| Remove leading zeros | 00123 | 123 | 00123 |
| Keep first 15 digits | 1234567890123456 | 1234567890123450 | 1234567890123456 |
| Convert digits around "E" | 123E5 | 12300000 | 123E5 |
| Letters and numbers to a date | JAN1 | January 1 | JAN1 |
With a setting off, the entry stays as text, exactly as typed. The first checkbox in the group turns all four on or off at once, and shows a mixed state when only some are on.
What still converts
These settings are narrower than they look. With all four off, entries like these still become dates:
1/2 // January 2
1-2 // January 2
JAN 1 // January 1
JAN-1 // January 1
3-Mar // March 3
2026-10-23 // October 23, 2026
Excel has no setting that stops this. To keep one of these as text, type an apostrophe first ('1/2), or format the cells as Text before you enter the data. The apostrophe works for any entry, whatever these settings say, and it doesn't show in the cell. Ordinary numbers are unaffected too: 0.5 and 123 are still numbers with every setting off.
Video: How to enter numbers as text in Excel
Typing, pasting, and opening files
The settings apply when you type a value, when you paste text, and when you open a CSV or text file in Excel. With the settings off, a CSV with a column of ZIP codes opens with the leading zeros intact.
The last checkbox in the group, "When loading a .csv file or similar file, notify me of any automatic data conversions", shows a message listing the conversions Excel is about to make when you open a file. Leave it on.
VBA ignores these settings. A value written with
Range.Valueand a file opened withWorkbooks.Openare converted as if every setting were on.
Where to find it
- Windows
- File > Options > Data > Automatic Data Conversion > Enable all default data conversions below when entering, pasting, or loading text into Excel
- Mac
- Excel > Preferences > Edit > Automatic Data Conversion > Enable all default data conversions below when entering, pasting, or loading text into Excel
- Applies to
- Excel itself, so every workbook
Recommendation
If you work with IDs, part numbers, account numbers, ZIP codes, or similar codes, turn off the conversions that damage them. The leading-zero and 15-digit settings matter most, because the data they change can't be recovered: the zeros and the extra digits are gone.
Scientists have a particular reason to turn off date conversion. In 2020, the committee that names human genes renamed genes like SEPT1 and MARCH1 (now SEPTIN1 and MARCHF1), partly because Excel kept turning them into dates.
If you mostly enter real numbers and dates, the defaults are fine. And when a value only needs to look different, a number format is the better tool: see Custom number formats.
Last verified October 28, 2026. All Excel options
