Excel export truncates really long numbers

  • 1
  • Question
  • Updated 3 years ago
  • Answered
I have a table which often gets exported to excel via the export feature. I have a field in the table called "Utility_Account_Number__c" which is a Text field. in some cases a number can be up to 20 numbers. When exporting to excel, excel treats these fields as number fields instead of text fields. This causes the number to get all weird. Firstly the numbers show up in a scientific notation kind of look. Secondly even when telling excel to treat it as text, it is still messed up. For example a number that looked like 00532432402341233453 will come out like 5324324023000000000. Basically leading zeroes get thrown out, and later numbers get converted to zeroes. Obviously these numbers need to be accurate and this is causing quite an issue. I suppose this could be considered an excel issue, but I am setting the field as text in SFDC, so shouldn't it retain that data type during the export?
Photo of Moshe Karmel

Moshe Karmel, Champion

  • 8,646 Points 5k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Moshe.  I'd contend that the export actually does work.  Its Excel that's not working right.  If you open your export csv in a text editor - I bet you will find that the leading zeros are still retained in the file.  

I've seen some resources that show ways of retaining the leading zeors..  https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-...
Photo of Moshe Karmel

Moshe Karmel, Champion

  • 8,646 Points 5k badge 2x thumb
You are correct sir!