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.

Microsoft Excel Format Cells dialog
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. 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.

Featured Video

Stop your Vizio smart TV from spying on you

CNET's Dan Graziano shows you how to disable Vizio's data collecting feature on your smart TV.

by Dan Graziano