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
6
This content is rated TV-MA, and is for viewers 18 years or older. Are you of age?
Sorry, you are not old enough to view this content.

Connect Android Wear to your iPhone

Android Wear now plays nice with Apple's iPhone. Here is how to get the watch and phone talking to each other, plus some cool things you can do.

by Lexy Savvides