Forum Discussion
Strange Paste Problem
I am an Excel novice and I having troubles with the Paste function in Excel. I have opened a new workbook and pasted four rows from another file on the company shared drive. I am attempting to edit a number of cells with new/corrected information by copying a number from a WORD document and paste it into the cell. To be specific, I am replacing 2019010305230350 with 2020970305000521, but when I paste the second number into the cell, I get “2.02097E+15” in the cell and “2020970305000520” in the formula bar. It does not seem to matter what cell I choice to paste into, the result is the same.
I have attempted to remove all formatting from the workbook, but I’m unsure if I have actually accomplished anything… can anyone suggest on how to disable and/or stop this?
3 Replies
- I should add that I can manually (type) dissimilar numbers into the cell, but anything close to the original number automatically changes to “2.02097E+15” in the cell and “2020970305000520” in the formula bar. Really odd…
Mike952 .... For data entry, Excel interprets up to only the first 15 significant digits, replacing any digits to the right with zeros. Moreover, when displaying numeric values, Excel formats up to only the first 15 significant digits, rounded, replacing any digits to the right with zeros.
You need to enter your data as text. Ostensibly, we should be able to make the cell format Text, then paste into. However, I'm not sure that is sufficient to override Excel's behavior, especially when the source is MSWord. Alternatively, we could prefix __each__ data value with an apostrophe (single quote). But of course, we can do that only one-by-one, not when pasting a column of "numeric" text, for example.
My approach is: paste into Notepad and save the "txt" file. Then __import__ the file into Excel using Data > Get External Data > From Text. In the last (third) dialog box of the Import Text Wizard, select the column(?) of data, then select Column Data Format > Text.
Errata: It is sufficient to paste the data into Notepad first. Then in Excel, format the cells as Text first, then copy-and-paste from Notepad directly into Excel. No need to save as a "txt" file first.
But note that any "numeric" text entered that way will lose precision on the right if you try to reference the data in numeric expressions.
JoeUser2004 Thank you for the suggestion !!! We found a similar workaround using the "text" idea. Since we did not have to use the number in a formula, we added a alpha prefix (followed by a hyphen) to the number (formatted as text) and that change allowed the cell entry/paste without Excel auto-changing the what got pasted into the cell.
