Avoid Excel to chop off the last digits of the numbers

copy/pastemicrosoft excelsql server

As indicated in this URL, the largest number Excel can handle is about 10^307.
Nevertheless, Excel seems to have problems with smaller numbers too (about 10^17) as you can see from my example:

I'm working on an SQL-server database, which contains barcodes as one of the fields in the tables. Those barcodes typically have 18 characters, like 154105070221597646.

When I copy such data from SQL-server database table (using Microsoft SQL Server Management Studio) into a standard MS-Excel worksheet, it get turned into a "general" number like 1.54105E+17), which seems to have as a real value 154105070221597000 (mind the last three digits who have been turned into zeroes).

Currently, I copy the data, change the format of that column into text and copy the whole thing again, which is working, but as I need to do this regularly I would like to have a simpler way for that.

How can I tell Excel to stop cutting the last digits of my numbers?

For your information: if I enter the barcode into a cell and do the "Convert to Number", this also happens as you can see from this screenshot:

enter image description here

Best Answer

First option - format the cells as text ahead of time. I don't much like this one, since it needs that extra step, but if the cells are already formatted as text BEFORE you paste it in, that formatting should be retained.

Second option - my preferred option - rather than copying from SSMS, use Save As to save the output to a tab-separated text file, and open that file in Excel using the Text Import Wizard. During this process, you can select the format of each column; selecting Text as the format should ensure that the data will be treated as a text string rather than a value.

enter image description here

Third option - use a simple macro to paste the values in. The Range.PasteSpecial method in VBA works differently from the PasteSpecial functionality in the user interface, and will paste values in as simple text if no other specification is given. The macro would be something like

Sub PasteAsText()
    Selection.PasteSpecial
End Sub

Of these options, I generally prefer the second option, as it gives you the most control over the data and doesn't rely on the sometimes finnicky clipboard.

Related Question