2

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

Dominique
  • 2,373

3 Answers3

3

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.

Werrf
  • 1,032
1

A tip that will help you reliably create a full textual copy of your data;

Prepend the barcode (-data) with an initial ' [1] as you generate / export the data from SQL; This will stop Excel from fiddling with it, as it (by this) is explicitly defined as "TEXT" as it arrives.

[1] i.e. CHAR(39) in Excel

Hannu
  • 10,568
1

Since you're working with a SQL Database this might be an option.

In SQL Server Management Studio, instead of doing a SELECT * FROM TABLE query, throw a concatenate in there so that the excel cell treats the data as text.

SELECT CONCAT('="', [Column], '"') FROM Table.dbo.Database

This will return Results like this:

enter image description here

Which, in Excel will look like this:

enter image description here

Lucretius
  • 146