Numbers Change to Dates in Excel
Have you ever copied football scores, such as 3-2, and pasted them into Excel, where they magically transform into dates? It's certainly annoying when a nice list of numbers change to dates in Excel, but fortunately there's an easy way to prevent that from happening.
Sample List of Numbers
If you're not quite sure what I'm talking about, copy the three numbers below, and paste them onto an Excel worksheet.
The results will vary slightly, depending on your Regional settings – Excel might see the numbers as a month/day or day/month
In the screen shot below, you an see what happened when I pasted the numbers into Excel. Two of the numbers change to dates, and the leading zero was dropped from the other number.
3/4 became March 4th, the leading zero was dropped from the second number, and 1-3 turned into January 3rd.
Thanks, Excel, but that's not quite what I wanted!
Don't Let Numbers Change to Dates
If you want to copy those types of numbers, and keep their original formatting, you just have to do a little extra work – nothing complicated.
First, get the worksheet ready:
- In Excel, select the columns where you're going to paste the data. If you're copying two columns from a website, select the same number of columns on the worksheet.
- Then, on the Ribbon's Home tab, in the Number group, click the drop down arrow, and click on Text – it's way down at the bottom of the list
After you've got the worksheet ready, follow these steps to paste the data and keep its formatting.
- Select the data and copy it
- On the worksheet, go to the column(s) that you formatted, and right-click the starting cell where you want to paste the data
- In the popup menu, under Paste Options, click Match Destination Formatting
The data will be pasted in its original formatting -- no more numbers change to dates!
Text Numbers Not Real Numbers
Of course, the pasted data is text now, not real numbers. You won't be able to add them up, or get an average, or have other kinds of number fun.
If you decide to change the text numbers to real numbers later, there are lots of ways to do that, such as using the Text to Columns command.
More Data Entry Tips
For more data entry tips, and troubleshooting ideas for copying and pasting, visit the Data Entry Tips page on my Contextures website.