No Ticket Needed

Appearance

4 min fix 11 min read Recently reviewedBeginner

By , IT support technician

Quick Answer

Select the cells before you type, press Ctrl + 1, choose Text on the Number tab, and click OK. For a single value, type an apostrophe first: '1-2.

How to Stop Excel Changing Numbers into Dates ​

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the web); Excel 2019 and later
Article Type: Troubleshooting
Last Updated: 2026-07-29

Summary ​

Excel reads anything that resembles a date as a date, so 1-2 becomes 2-Jan and the leading zero vanishes from 02134. This article shows you how to stop the conversion before you type, how to rescue a column that has already been converted, and how to import CSV files without Excel rewriting them on the way in.

Symptoms ​

  • You type 1-2 and the cell shows 2-Jan.
  • Product codes such as 03-05 display as 3-May or 5-Mar, depending on your region settings.
  • Leading zeros disappear: 02134 becomes 2134, and 007 becomes 7.
  • Long account or invoice numbers switch to scientific notation such as 1.23457E+15, and the final digits turn into zeros.
  • Fractions typed as 1/2 show as 2-Jan.
  • Setting the cell format to Text afterwards replaces the date with a five-digit number such as 46024.
  • The same file breaks the same way every time you open it, even after you fix the cells and save.

Prerequisites ​

  • Any version of Excel. The two options that are version-specific are called out in the step that uses them.
  • Edit permission on the workbook — the fix changes cell formats.
  • No admin rights required.

Instructions ​

1. Format the Cells as Text Before You Type ​

This is the fix that works every time, and the order is the whole point: format first, type second.

  1. Select the cells you are about to fill. Click the column letter at the top of the sheet to select the entire column.
  2. Press Ctrl + 1 to open the Format Cells dialog. On a Mac, press Command + 1.
  3. On the Number tab, select Text at the bottom of the Category list on the left.
  4. Click OK. The dialog closes and the cells look unchanged.
  5. Type your values. 1-2 stays 1-2, and 02134 keeps its leading zero.

Verification: Click one of the cells you filled. The value sits against the left edge of the cell instead of the right — that is Excel showing you it is holding text, not a number.

Note: Excel for the web has no Format Cells dialog. Open the Home tab, click the number format dropdown in the Number group — it reads General — and choose Text at the bottom of the list.

Note: Text-formatted cells do not calculate. =SUM() across a Text column returns 0. Use Text for codes, IDs, ZIP codes and part numbers — anything you will never do arithmetic on.

2. Type a Leading Apostrophe for One-Off Entries ​

Use this when a single cell needs to behave and you do not want to change the format of a whole column.

  1. Click the cell.
  2. Type an apostrophe, then your value, with no space between them: '1-2.
  3. Press Enter. The cell shows 1-2. The apostrophe is a formatting instruction, not part of the value, and it never prints.
  4. Click back on the cell. The formula bar shows '1-2 with the apostrophe; the sheet shows 1-2 without it.

Tip: A small green triangle appears in the top-left corner of the cell, and clicking the warning icon beside it shows Number Stored as Text. That is expected here. Choose Ignore Error from that menu to clear the triangle.

3. Why Reformatting Afterwards Does Not Bring It Back ​

This is the part that catches people out, and knowing it saves you twenty minutes of trying formats that cannot work.

  1. Excel stores every date as a serial number counted from 1 January 1900. 2-Jan-2026 is held as 46024, and the date you see on screen is a display format sitting on top of that number.
  2. The moment you press Enter on 1-2, the characters you typed are discarded. Excel keeps 46024 and nothing else.
  3. Changing the cell format to Text afterwards changes only the display, so the cell shows 46024. There is no copy of 1-2 left in the file to restore.

Note: Press Ctrl + Z immediately and the conversion is undone along with the entry, leaving an empty cell you can format as Text before retyping. Undo only helps in the seconds after the entry — once you save and close the workbook, it is gone.

4. Fix a Column Excel Has Already Converted ​

Pick the method that matches the size of the damage.

For a short column, retype it. Format the cells as Text using Step 1, then enter the values again from your original source.

For a long column of date-like codes, rebuild the text from the serial number. This works when every value followed the same pattern.

  1. Insert a blank column to the right of the converted one.
  2. In its first cell, enter a formula that reproduces the original pattern. For values that started as 1-2, use:
=TEXT(A2,"m-d")
  1. Double-click the small square at the bottom-right corner of the cell to copy the formula down the column.
  2. Select the new column, press Ctrl + C, right-click the first cell of the original column, and choose the Values icon under Paste Options.
  3. Delete the helper column.

For lost leading zeros, the underlying number is intact, so a custom format restores them on screen.

  1. Select the column and press Ctrl + 1.
  2. On the Number tab, select Custom at the bottom of the Category list.
  3. In the Type box, replace the contents with 00000 — one zero for each digit the value needs.
  4. Click OK. 2134 now displays as 02134.

Note: A custom format changes the display only. The cell still holds the number 2134, so exporting to CSV writes 2134 again. To make the zeros part of the value, build a helper column with =TEXT(A2,"00000") and paste the result back as values.

Tip: If the converted column now shows a row of hashes instead of dates, the column is too narrow rather than broken — see How to Fix "######" Errors in Excel Cells.

If the data came from a CSV or a system export, do not repair it in the sheet. Clear the sheet and re-import with Step 6, which sets the column types before any value is converted.

5. Turn Off Automatic Data Conversion in Excel 365 ​

Excel for Microsoft 365 on Windows added a group of switches that cover several of these conversions. They apply to Excel on this computer, in every workbook you open.

  1. Click File in the top-left corner, then Options at the bottom of the left sidebar. The Excel Options dialog opens.
  2. Select Data in the list down the left side of the dialog.
  3. Find the Automatic Data Conversion heading in the main pane.
  4. Clear the checkboxes for the conversions you want stopped:
    • Remove leading zeros from numerical text and convert to a number — clearing this keeps 02134 whole.
    • Truncate numeric data to 15 digits of precision and convert to a number if the number may lose precision — clearing this keeps long account numbers from ending in zeros.
    • Convert digits surrounding the letter "E" to a number in scientific notation — clearing this keeps codes such as 1E5 as text.
    • Convert continuous letters and digits to a date — clearing this keeps entries such as SEP2024 as text.
  5. Leave When loading a .csv file or similar file, notify me of any automatic number conversions ticked, so Excel tells you when an import has changed something.
  6. Click OK. The dialog closes and the settings take effect on the next value you enter.

Note: These switches do not cover the 1-2 to 2-Jan case. A dash or slash between two numbers is read as a date in every version of Excel, with no setting to turn it off. Steps 1 and 2 are the fix for that one.

Note: No Automatic Data Conversion heading on the Data page means your Excel predates version 2309. Check File > Account > About Excel for your version number. The Text format and apostrophe methods work in every release.

6. Import CSV Files with Data > From Text/CSV ​

Double-clicking a CSV gives Excel no chance to ask you anything — it converts as it opens. Import the file instead.

  1. Open a blank workbook, or a blank sheet in your existing workbook.
  2. On the Data tab, in the Get & Transform Data group at the far left, click From Text/CSV.
  3. Select your file and click Import. A preview window opens showing the first rows of the file.
  4. Open the Data Type Detection dropdown along the top of that preview and select Do not detect data types. The preview redraws with every column as text.
  5. Click Load. The data lands in a new sheet as a table, with 03-05 and 02134 exactly as the file holds them.

To keep some columns as numbers and others as text:

  1. In the preview window, click Transform Data instead of Load. The Power Query Editor opens in its own window.
  2. Right-click a column header, point to Change Type, and select Text. If a Change Column Type dialog appears, click Replace current.
  3. Repeat for every column that must stay as typed, then click Close & Load on the Home tab at the top-left.

Tip: The import is saved with the workbook. When the source file changes, click any cell in the imported table and choose Data > Refresh All. Your column types are reapplied on every refresh, so the file cannot break itself again.

Note: CSV files bring their own set of problems beyond date conversion — stripped leading zeros, long numbers truncated to scientific notation, and accented characters arriving as é. For the full import routine including encoding and delimiter settings, see How to Open a CSV File in Excel Without Breaking It.

Troubleshooting ​

WARNING

Formatting a cell as Text after Excel has converted your entry does not recover it — the characters you typed are already discarded and the cell holds a date serial number. Format first, then type. If a column has already been converted and you have no copy of the source data, Step 4 rebuilds what it can, but re-importing from the original file is the reliable route.

Symptom / ErrorPotential CauseSolution
Typing 1-2 gives 2-JanDash between numbers read as a dateFormat the cells as Text before typing (Step 1), or type '1-2 (Step 2).
Cell shows a five-digit number such as 46024Format changed to Text after conversionThe value is a date serial number. Rebuild it with the TEXT formula in Step 4, or retype the column.
Leading zeros dropped from ZIP codesExcel strips zeros from numbersClear the leading-zeros switch (Step 5), or apply the custom 00000 format (Step 4).
Long account numbers end in zerosExcel keeps 15 digits of precisionFormat the column as Text before pasting (Step 1) and clear the truncation switch (Step 5).
The same CSV breaks every time you open itDouble-clicking converts on openImport with Data > From Text/CSV and set Do not detect data types (Step 6), or follow How to Open a CSV File in Excel Without Breaking It.
=SUM() over the column returns 0Text-formatted cells do not calculateExpected. Keep Text for codes only. To convert genuine numbers back, see How to Fix Numbers Stored as Text in Excel.