Our customer ID field is alphanumeric and permits leading zeros. When we export reports to CSV and then open them in Excel the leading zero is removed. Is there any way to get Salesforce to prefix this field with an apostrophe or other mark that would designate the data as text and prevent Excel from automatically removing the leading zeros?
Edit: Salesforce addresses the issue with a workaround that I would rather avoid, if possible.
Edit 2: This field is encrypted and cannot be used in formulas.
Attribution to: Mike Chale
Possible Suggestion/Solution #1
You could try creating a new Formula field (Type: Text) on your Customer Object that has a value of a single quote prepended to the Customer Id. Then use this field in your report that your exporting:
"'" & Id
or
"'" & CustomerId__c
Attribution to: Jon Hazan
Possible Suggestion/Solution #2
I know when opening a CSV in LibreOffice Calc you can select a column in the preview pane and change its format to "Text". Upon opening the leading zeros appear correctly for any columns you carry that operation out on. I would have assumed Excel has similar functionality.
Attribution to: David Gillen
Possible Suggestion/Solution #3
I had go with the suggestion from @Jon Hazan above, if you must open this CSV in excel for a purpose. However, if you can try to work this data without having to Open in Excel, much better as Excel tends to apply its own transformation when opening files and interpreting values.
The other option if you must use Excel, I would recommend you do a File --> Import instead and let the wizard treat your desired ID values as Text and you can retain your values as you need them. Hope this helps.
Attribution to: Planet Bips
Possible Suggestion/Solution #4
if you know the field has leading zeros, then you can format that in Excel with 000000 as the format string (depending on how many zero's a value should have).
If there's not a fixed number of characters for the column, then this won't work
Attribution to: SeanC
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/1129