How To: Stop Excel From Changing Numbers to Dates



RECOMMENDED: Click here to fix Windows errors and optimize system performance

Microsoft Excel is pre-programmed to facilitate entering of dates. For example, 12/2 becomes 2-Dec, which is very frustrating when you enter something you don’t want to change for a date. Unfortunately, there is no way to turn it off. But there are ways to get around it.

Preformat the cells in which you want to enter numbers as text. This way, Excel will not try to change what you type in the data.

How To Prevent Excel From Changing Numbers to Dates:

Trick Excel

You can convince Excel that you are not entering numbers by adding another character before the number or string. In this way, you can prevent Excel from converting numbers into data. Some common methods are:

  • Add an apostrophe (‘) before the value. This has essentially the same effect as setting the number format to text. The good thing is that you won’t see an apostrophe in the cell.
  • Another method is to add a space. However, we must mention that this is not the best method, because sometimes space can affect the values or formulas of cells.
  • Another trick, especially for fractions without integers, is to add a 0 and a space. This combination can act as the integer part and prevent the value from becoming a number. Excel also removes zeros and spaces automatically.


Update:

We now recommend using this tool for your error. Additionally, this tool fixes common computer errors, protects you against file loss, malware, hardware failures and optimizes your PC for maximum performance. You can fix your PC problems quickly and prevent others from happening with this software:

  • Step 1 : Download PC Repair & Optimizer Tool (Windows 10, 8, 7, XP, Vista – Microsoft Gold Certified).
  • Step 2 : Click “Start Scan” to find Windows registry issues that could be causing PC problems.
  • Step 3 : Click “Repair All” to fix all issues.

download



The apostrophe method

By placing an apostrophe in the cell before the number, you tell Microsoft Excel to ignore the cell formatting and display the number as it was entered in the cell. This method means that even if someone changes the formatting of the cell in general and tries to edit a cell, it will still look the same instead of being automatically formatted by Excel.

  1. Type an apostrophe in the cell.
  2. Enter the number as you want it to appear and press Enter.
  3. The numbers must now be correctly displayed and imported.
  4. This allows you to use the MATCH and VLOOKUP functions in Excel (the apostrophe is ignored).

Use of the code

If you find the above methods too complex, Excel can be automatically configured to force “Text” formatting in all workbooks. To do this, part of the code must be entered in the Workbook Code module. To do that:

Select the following code, right-click it and select “Copy”.

Private Sub Workbook_Open()
Dim sh As Worksheet

For Each sh In Me.Sheets
sh.Cells.NumberFormat = "@"
Next

End Sub
  1. Open the workbook in which you want to insert the code.
  2. Press simultaneously the “Alt” + “F1” keys.
  3. Click on “Insert” and select “Module”.
  4. Select “Edit” where the cursor blinks and select “Paste”.
  5. Click on “OK” to add it.
  6. Click on the View tab and select Macros.
  7. Select the added code to execute it.

https://superuser.com/questions/552042/how-to-disable-excel-number-autoformat



RECOMMENDED: Click here to troubleshoot Windows errors and optimize system performance