Stop Excel from Rounding Long Numbers on Paste

Copper Contributor

This has been asked many times, in many places, and I still don't have a functional solution. I'm looking for some sort of permanent setting that will prevent Excel from rounding pasted long numbers. I'm copying a long number from a website and pasting into Excel. Let's use this as the example: a video ID of 1193798918192507 . When pasting, Excel converts that number to: 1.1938E+15, or 1193798918192500. Now I have an entirely different ID. 

 

I need Excel to retain the actual ID of 1193798918192507 so that I can use a vlookup function.

 

Here's what doesn't work:

1. Formatting the cell as number before or after the paste.

2. Formatting the cell as text before or after the paste.

3. Formatting the cell as custom "################" before or after the paste.

4. Formatting the cell as fraction before or after the paste.

5. Creating a spreadsheet somewhere else to input these numbers before importing to Excel. This isn't practical unless you already have a long list of numbers assembled.

6. Typing an apostrophe before each paste - I don't want to navigate my hand from keyboard to mouse a hundred times. A mouse click to copy, click to paste, is most efficient. 

7. Pasting all the long numbers and using a formula to modify the number to text in another column. Concatenating an apostrophe, or using (=A1,""), or any other formula will only provide the number output AFTER Excel has already rounded. Not helpful, those last digits are identifiers and need to be what they are and cannot be replaced with zeroes. 

 

Is there some sort of permanent way that will stop Excel from rounding these numbers? If not, is there anywhere that we can submit requests to their engineering team?

 

*Please do not provide a solution unless you've copied & pasted the above number and it actually shows up in Excel as the correct number ending in -507 (without using methods 1-7 detailed above). I'm not trying to be ungrateful for people taking time out of their day to assist, I'm just trying to find a solution that actually works for the thousands of us with this problem.

 

Many thanks in advance for your help!

 

1 Reply

@AubreyGem 

The limitation of 15 digits is defined in the Excel specification, unfortunately you cannot change it.

This limitation has applied to all versions of Excel for years.

You probably know this, judging from your text :).

 

The only thing that comes to mind is to format the cells/ranges/worksheet as text in advance.

Then paste the copied numbers in the paste options with "Match line formatting (M)".

So you get all the numbers exactly as you had them before.

You can then reformat the cells as numbers using "Format Cells...".

 

I very much doubt whether you can also carry out calculations with it, because the +15 would appear again.

 

I hope that this information could help you a little.

 

NikolinoDE

I know I don't know anything (Socrates)