Your browser (Internet Explorer 6) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.
X
Post

How to 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.

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.

Leave a comment  

name*

email*

website

Submit comment