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.

list of numbers pasted into Excel

Sample Data to Copy

To see an example of the problem, follow these steps:

  1. First, copy the list of 5 numbers below.
  2. Next, paste the five numbers onto an Excel worksheet
3/4
02345
1-3
Mar1
4Jan

Pasted Number Results

In the screen shot below, I pasted the five numbers into column B.

Excel automatically changed every number to something different:

  1. Number fraction, 3/4, was changed to a date: March 4th, formatted as d-mmm
  2. Number with leading zero, 02345, was changed to a number without zero: 2345
  3. Numbers separated by hyphen, 1-3, was changed to a date: January 3rd, formatted as d-mmm
  4. Continuous letters and numbers, Mar1, was changed to a date: March 1st, formatted as d-mmm
  5. Continuous letters and numbers, 4Jan, was changed to a date: Jan 4th, formatted as d-mmm

list of numbers pasted into Excel

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

list of numbers pasted into Excel

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:

  1. Number fraction, 3/4, WAS changed to a date
  2. Number with leading zero, 02345, was NOT changed
  3. Numbers separated by hyphen, 1-3, WAS changed to a date
  4. Continuous letters and numbers string, Mar1, was NOT changed
  5. Continuous letters and numbers string, 4Jan, was NOT changed

list of numbers pasted into Excel

Related Links

Sample Data (for testing)

More Sample Files

Excel Topics

 

Last updated: December 6, 2023 2:41 PM