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:
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.
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.
- Type an apostrophe in the cell.
- Enter the number as you want it to appear and press Enter.
- The numbers must now be correctly displayed and imported.
- 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
- Open the workbook in which you want to insert the code.
- Press simultaneously the “Alt” + “F1” keys.
- Click on “Insert” and select “Module”.
- Select “Edit” where the cursor blinks and select “Paste”.
- Click on “OK” to add it.
- Click on the View tab and select Macros.
- Select the added code to execute it.
RECOMMENDED: Click here to troubleshoot Windows errors and optimize system performance
CCNA, Web Developer, PC Troubleshooter
I am a computer enthusiast and a practicing IT Professional. I have years of experience behind me in computer programming, hardware troubleshooting and repair. I specialise in Web Development and Database Design. I also have a CCNA certification for Network Design and Troubleshooting.