Numbers Change to Dates in Excel

Have you ever copied football scores, such as 3-2, and pasted them into Excel, where they magically change to 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.

  • 3/4
  • 02345
  • 1-3

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 were changed 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.

numberdates05

Thanks, Excel, but that’s not quite what I wanted!

Prevent Number Formatting Loss

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

numberdates01

Paste Carefully

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

    numberdates02

The data will be pasted in its original formatting, instead of changing 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.

numbertext04

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.

__________________