Manage updates with the Download App
Prevent Excel from reformatting two numbers to a date and month
Type an apostrophe before entering or pasting two numbers separated by a hyphen in Microsoft Excel to keep the numbers from being converted to their month and date equivalent.
Topping my list of Microsoft Excel pet peeves is the program's automatic conversion of two numbers on either side of a hyphen into the date and month the numbers correspond to, such as "1-1" becoming "01-Jan."
The change occurs even when you choose any of the options available when you select Paste Special. I have looked high and low for a way to disable this setting in Excel, to no avail.
You can make the change for an entire worksheet by clicking the small box above 1 and to the left of A in the top-left corner, or by pressing Ctrl-A or Command-A to select all the sheet's cells. Then right-click any cell, choose Format Cells, and select Text in the left pane under the Number tab.
Choose Text under the Numbers tab of Excel's Format Cells dialog to prevent the program from converting two date-like numbers to a date and month.
(Credit: Screenshot by Dennis O'Reilly/CNET)There's also a one-keystroke solution: type an apostrophe before entering or pasting a pair of numbers that Excel could mistake for a date and month. When you exit the cell, the apostrophe vanishes and the numbers stay numbers, formatted as text.
