Is there a VBA to fix my issue with pasting 20 digit numbers and Excel erasing the last part and adding zeros?

[Llestyle89]

Is there a VBA or formula for fixing the issue where Excel erases the last part of my 20 digit number and adds a bunch of zeros to my number instead?
I've read all the post about just clicking "Match Destination" or after I paste, then change the cell format to text etc. I've tried formatting the cell before hand but as soon as I paste my number, Excel changes the cell format to General.
My issue is, my spreadsheet is going to be shared with people who are not good with excel or computers at all. They basically know how to copy and paste and that's it. So having to do any extra formatting or clicks will not work.
I'm looking for maybe a Macro or formula that I can use that will automatically keep the number like its suppose to be once they paste the number in the assigned cell.
Not sure if it matters, but we copy the 20 digit number from a web site which I think then tries to pasted into Excel as html format. I've search all over the internet and can't find what I'm looking for. Pasting the number into NotePad and then coping it again to paste in Excel is not an option as it will just be too big of a pain in the butt to do 300 times a day.

Is there anyone available that could help me? Thanks

 

[RCBricker]

pasting from website will try and paste it HTML, however, if the user simply right clicks they can then select text. Given your limitations I can not think of anything that would stop excel. the problem is that excel wont know what it dropped so it cant replace the values.

 

[SpillerBD]

This archive is still valid regarding the 15 digit limit in Excel Excel Large Numbers
For me the question would be, "Is your number representing a value, or is it really a label like a credit card number?
If its a label, treat the value as text.
If it is a number that you need calculations on then use the search term "arbitrary precision software spreadsheet" in your web-search engine.
Unfortunately you may have some issues that may take some time to resolve....

 

[Llestyle89]

There will be not calculations with these numbers. I just have a few macros that run once the number is copied correctly to a cell, and then the macros copies and paste these numbers to various tabs and cells in my document but because it doesn't paste right, the macro is unless. So hopefully I can figure something out that runs automatically.

 

[SpillerBD]

Treat the data as text. I have developed a theory that unless it is an actual number it should be handled as text and if in doubt handle as text.
I would run in to issues with data consistency or issues where leading zeroes do matter. If a data field needed to changed to number (General) you could do that. But once Excel convert the text to number you can't say how many zeroes where in the original source.