Why does 1 1

Prevent the automatic conversion of numbers to dates

Microsoft Excel is preprogrammed to make entering dates easier. For example, 12/2 changes to Dec 2. This is very frustrating when you enter something that you don't want to be changed into a date. Unfortunately there is no way to turn this off. There are ways to get this caught up, however.

Format the cells you want to enter numbers in as text beforehand. Then Excel does not try to convert the entered values ​​into date values.

If all you have to do is enter a few numbers, here are some ways to prevent Excel from converting the numbers to dates:

  • Enter a space in front of a number. The space is retained in the cell even after you press ENTER. (See notes)

  • Enter an apostrophe (‘) in front of a number, e.g. B. ’11 -53 or ‘1/47. The apostrophe does not appear in the cell after you press ENTER.

  • A zero and a space before entering a fraction, e.g. B. 1/2 or 3/4 so that they are not in 2.01. or March 4th. Enter 0 1/2 or 0 3/4. After you press ENTER, the zero does not stay in the cell and the cell becomes a fraction number type.

  1. Highlight the cells you want to enter numbers in.

  2. Press Ctrl + 1 (the 1 on the regular, not the numeric keypad) to bring up the dialog box Format cells to open.

  3. click on text and then on OK.

  1. Highlight the cells you want to enter numbers in.

  2. click You on start > Number format>text.


  • We recommend that you use an apostrophe instead of a space to enter data if you want to use lookup functions on the data. Functions like MATCH and VLOOKUP will miss the apostrophe when calculating the results.

  • When a number is left-justified in a cell, it usually means that it is not formatted as a number.

  • If you enter a number that contains an "e" (e.g. 1e9) it is automatically converted to a scientific number: 1.00E + 09. If you don't need a scientific number, add an apostrophe before the number: ‘1e9.

  • Depending on the number you entered, you may see a small green triangle in the upper left corner of the cell indicating that a number is stored as text, which is an error for Excel. Either ignore the triangle or click on it. A field appears on the left. Click in the field and select then ignore errorswhich removes the triangle.

Do you need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers Community.