Exporting to Excel: Number Format Issue

[Bennie47250]

Crystal Version 7.0
Exporting to Excel version 5.0 Opening in Excel 2002

I have a report that I’m designing to export to Excel. One of the groupings is by the General Ledger account number, a twelve position number (with no decimals or commas).

I’m showing the groupings on the report. In Crystal, in the preview view, the number looks like 220,145,960,000.00. I’m able to format the number so it looks like 220145960000 by right clicking on the number and applying the appropriate formatting options. However when the report is exported to Excel, it reverts to 220,145,960,000.00. I thought I could overcome this by writing a formula to convert the number to text, and formatting the number to show without decimals or commas. The formula used is

ToText ({GL_Account.Acct#},0 ,"" )

And while this shows correctly in the preview, when it is exported to Excel, I have the same old problem. Is there a workaround for this?

TIA
Bennie

 

[PerrinA]

In Excell your cells are formated to give you commas. All you have to do is select the cells that have the problem, right click on them and click on Format Cells. Once here click on the Number tab. There you can either choose to make the Cells General, which wouldn't put the comma in but would also remove the decimal. The best option is to leave it selected on number but deselect the check box marked Use 1000 separator (,). Once you do that you should not see the comma anymore.

 

[Bennie47250]

Thanks for the suggestion. What you have suggested will work but I was hoping for something that the user would not have to do after they open the sheet. I think I have found this by tweaking my formula.

Apparently Excel thinks the result of the formula is a Number so it applies the number format to it. By putting a blank space in front of the number, Excel no longer considers it a number and does not add the formatting. My formula now looks like this.

“ “ + ToText ({GL_Account.Acct#},0 ,"" )

Bennie