Home > Data > Troubleshoot > Paste Numbers
Excel Data Problem Pasting Numbers
If you copy numbers such as 1-4 or text and numbers, like Mar1, and paste them into Excel, they
will usually change to dates. In Excel 365, you can change an Option setting, to prevent some of the number conversion problems.
Sample Data to Copy
To see an example of the problem, follow these steps:
- First, copy the list of 5 numbers below.
- Next, paste the five numbers onto an Excel worksheet
Pasted Number Results
In the screen shot below, I pasted the five numbers into column B.
Excel automatically changed every number to something different:
- Number fraction, 3/4, was changed to a date: March 4th, formatted as d-mmm
- Number with leading zero, 02345, was changed to a number without zero: 2345
- Numbers separated by hyphen, 1-3, was changed to a date: January 3rd, formatted as d-mmm
- Continuous letters and numbers, Mar1, was changed to a date: March 1st, formatted as d-mmm
- Continuous letters and numbers, 4Jan, was changed to a date: Jan 4th, formatted as d-mmm
Excel Options - Data Conversion
In Excel 365, you can change Option settings, to prevent some automatic data conversion problems.
To change the option settings, follow these steps:
- At the top left of Excel, click the File command.
- Next, in the category list at the left, go to the bottom, and click Options
- In the Options window, at the left, click the Data category
- At the right side, scroll down to the section named Automatic Data Conversion
- In the list of option settings, remove the check mark for any conversions that you don't want to occur automatically.
- Click OK, to save the changes, and to close the Excel Options window
Paste After Chaning Options
In this example, I turned off two of the data conversion options:
- Remove leading zeros and convert to a number
- Convert continuous letters and numbers to a date
In the screen shot below, Excel automatically changed two of the numbers, and left three numbers as they were:
- Number fraction, 3/4, WAS changed to a date
- Number with leading zero, 02345, was NOT changed
- Numbers separated by hyphen, 1-3, WAS changed to a date
- Continuous letters and numbers string, Mar1, was NOT changed
- Continuous letters and numbers string, 4Jan, was NOT changed
Sample Data (for testing)
More Sample Files