How to Stop Excel from Changing Numbers

Excel’s automatic number formatting can be a real headache—it turns long IDs into scientific notation, strips leading zeros from zip codes, and converts things like “1-2” into dates. This guide shows you the most reliable ways to keep your numbers exactly as you type them, whether you’re entering data manually or importing it from another source.

What You’ll Need

Microsoft Excel (2016, 2019, 2021, or Microsoft 365)
Access to the file or data source you’re working with

Step-by-Step

Use the Apostrophe Trick for Manual Entry
When typing a number that Excel might reformat (like a leading-zero ZIP code or a long ID), type an apostrophe (') before the number. For example, type '00123. The apostrophe won’t show in the cell, and Excel will treat the entry as text, preserving the exact format.
Pre-Format Cells as Text
If you’re entering a whole column of numbers that must stay as text (e.g., phone numbers, product codes), select the cells, go to the Home tab, and in the Number group, choose Text from the dropdown. Now anything you type will be stored as text, and Excel won’t apply its own number formatting.
Import Data Using the Text Import Wizard
For CSV files or other text imports, use Data > From Text/CSV (or Get & Transform). In the preview window, select the column(s) with numbers you want to keep as text, then choose Text as the data type before loading. This prevents Excel from auto-converting long numbers to scientific notation.
Use Paste Special to Keep Values as Text
If you’re copying numbers from another source, copy them, then right-click the destination cell and choose Paste Special > Values. Then, with the pasted cells still selected, change the format to Text (as in step 2). This works well when the source already has the numbers as text.
Disable AutoCorrect for Fractions and Dates
Go to File > Options > Proofing > AutoCorrect Options. On the AutoFormat As You Type tab, uncheck the boxes for Fractions (1/2) and Dates (1/2/2001). This stops Excel from turning typed fractions into date-like values.
Use a Custom Number Format for Display
If you only need the number to display a certain way (like showing leading zeros or full 15-digit IDs) but don’t need it as text, select the cells, right-click, choose Format Cells, then Custom. Enter a format like 00000 for 5-digit ZIP codes, or 0 for large numbers. This changes the display without altering the underlying value.
For CSV Files: Open with a Text Editor First
If you’re opening a CSV directly in Excel, it will often mangle numbers. Instead, open the CSV in a plain text editor (like Notepad), copy the content, and paste it into Excel using the Text Import Wizard (step 3). This gives you control over each column.

Common Mistakes

Applying Text Format After Typing: Changing a cell to Text format after you’ve already typed a number doesn’t undo the conversion. You must set the format before entering the data.
Using the General Format for Long Numbers: The General format automatically switches to scientific notation for numbers longer than 11 digits. Always use Text or a custom format for such values.
Forgetting the Apostrophe When Pasting: The apostrophe trick only works when you type directly. If you paste data that already has a leading apostrophe, it may show up as a literal character.
Relying on Number Format for Leading Zeros: A number format like 00000 only changes the display. If you later copy the cell, the underlying value (without zeros) is what gets copied.

Tips

Use a Template: If you regularly import data that needs text formatting, create a template with the columns pre-formatted as Text. This saves time on every import.
Check the Data Type in Power Query: When using Get & Transform, you can set the data type for each column to Text. This is the most reliable method for large datasets.
Use Flash Fill for Quick Fixes: If you have a column of numbers that Excel has already mangled, you can use Flash Fill (Ctrl+E) to recreate them as text. Type the desired value in a new column, then press Ctrl+E to fill the rest.
Keep IDs as Text in Databases: If you’re exporting from a database, ensure the export format treats the fields as text (e.g., quote them in CSV). This prevents Excel from interpreting them as numbers.

Troubleshooting

Excel Still Changes Numbers After Setting Text Format: Make sure you selected the cells before typing. If the data is already entered, you’ll need to re-enter it or use Flash Fill.
CSV Import Doesn’t Show the Text Import Wizard: In newer Excel versions, the wizard is hidden. Use Data > From Text/CSV instead of double-clicking the file. If you double-click, Excel opens the file directly with default settings.
Pasted Data Comes in as Scientific Notation: Copy the data as text from the source (e.g., use “Paste Special > Text” in the source app). If that’s not possible, paste into a text editor first, then copy and use the Text Import Wizard.
Leading Zeros Disappear When Saving as CSV: CSV files don’t store formatting. If you save a sheet with text-formatted numbers as CSV, the leading zeros will be lost when reopened. Save as Excel Workbook (.xlsx) instead.

Frequently Asked Questions

Why does Excel turn long numbers into 1.23E+15?
Excel’s default General format switches to scientific notation for numbers with more than 11 digits to save space. The underlying value is still accurate up to 15 digits, but it displays in scientific notation. Use a Text format or a custom number format to display the full number.

How do I keep leading zeros in Excel?
Type an apostrophe before the number (e.g., '00123) or pre-format the cells as Text. Alternatively, use a custom number format like 00000 to display leading zeros without changing the value.

Can I stop Excel from converting “1-2” to a date?
Yes. Disable the AutoFormat option for dates: go to File > Options > Proofing > AutoCorrect Options, and on the AutoFormat As You Type tab, uncheck Dates. Or pre-format the cells as Text.

What is the best way to import a CSV without losing number formats?
Use Data > From Text/CSV, and in the preview, set each column’s data type to Text for columns with numbers that must stay as text (like IDs or phone numbers). This ensures they import exactly as they appear.

Does the apostrophe trick work for pasted data?
No. The apostrophe only works when you type directly into a cell. For pasted data, use the Text Import Wizard or pre-format the destination cells as Text before pasting.

Why does my number lose its formatting when I save as CSV?
CSV is a plain-text format that doesn’t store cell formatting. Any leading zeros or text formatting will be lost when you reopen the CSV. Save as an Excel Workbook (.xlsx) to preserve formatting.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →