0

Querying from Excel to a SQL Server database with fields of data type Decimal(26,11). In other words, 26 digits before the decimal, plus 11 digits after the decimal. For example:

12345678901234567890123456.12345678901

Excel truncates them to 15 significant digits even if the column is preformatted as Text. In this example:

12345678901234500000000000.00000000000

How can we make Excel stop truncating them to 15 significant digits?

BTW we do understand that we won't be able to do math on the numbers in Excel without losing precision. We just want to get them as text in Excel, querying fields from SQL Server with data type Decimal(26,11). Any suggestions?

Greg Lovern
  • 178

1 Answers1

0

It works fine for me to use 12345678901234567890123456.12345678901 since my system decimal separator is "," not ".". So a stupid fix is going to options and change that.
If possible, you could try adding ' in front of the number, to keep it from doing this.

Also duplicate: Why is Excel truncating my 16-digit numbers?

Community
  • 1
Christofer Weber
  • 1,323
  • 1
  • 8
  • 14
  • Thanks, but that this will be used by several nontechnical users, so it isn't practical to expect them to change their system decimal separator.

    Also the other question you linked to isn't a duplicate because we're aware of Excel's limitation with numbers and the standard ways of dealing with them (format as text, put an apostrophe in front), but in this case they either aren't working (preformat as text) or are not practical (put an apostrophe in front).

    We don't have control of the database, and putting an apostrophe in front in the database would require a different data type anyway.

     – Greg Lovern Oct 21 '16 at 00:08
  • @Greg Lovern Duplicate or not, the answers from that question is probably the best you'll find. The addin option is probably the best, if it's possible to distribute among the nontechnical users. – Christofer Weber Oct 21 '16 at 05:46