1619352 - Number formatting not retained when exporting a report to MS Excel format, from Crystal Reports

• Thousands separator character, and decimal character, are different in MS Excel.
• Report exported to MS Excel format does not retain the format for the decimals and thousand separators.
• Format specified for a numeric value in Crystal Reports for the thousand separator and decimal character are different when exported to MS Excel.

• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

1. In Crystal Reports, create a report of any data source that contains numeric database fields.
        
2. Format the numeric fields to display a space for thousand separator:
   • 2.1 Right click on the numeric database field and select "Format Fields..."
   • 2.2 In the Format Editor window, under the tab: Number, click on the button: Customize
   • 2.3 In the Custom Style window, set the formatting option: Thousand Separator Symbol to a space ( )
      
3. Format the numeric field to display a comma for decimals
   • 3.1 Still in the Custom Style window, set the formatting option: Decimal Separator to a comma (,)
   • 3.2 Click OK to accept the change
   • 3.3 Click OK again to accept the change
        
4. Export the report to MS Excel format
       
5. Open the document in MS Excel, and notice the thousand separator is a comma, and the decimals is a dot instead of a space and a comma.

• MS Excel does not have all of the numerical formatting option Crystal Reports has. There is no option to change the character used for decimals and thousand separator for a cell in MS Excel, and this is why when exporting a report to MS Excel format, those options are not retained.
     
• In MS Excel the decimals and thousands separator format is based on the regional setting set in MS Windows.

• As a workaround to retain the format of your numeric value when exporting to MS Excel, you can convert your number to text using the function: ToText

1. Create a formula using the ToText function to convert the number into string, into the desired format, the formula will look like:

ToText({Numerical Database Field},2," ",",")

Where the arguments for the ToText functions are: 
 
- x is a Number or Currency value to be converted into a text string; it can be a whole or fractional value.
- y is a whole number indicating the number of decimal places to carry the value in x to (This argument is optional.).
- z is a single character text string indicating the character to be used to separate thousands in x. Default is the character specified in your International or Regional settings control panel. (This argument is optional.)
- w is a single character text string indicating the character to be used as a decimal separator in x. Default is the character specified in your International or Regional settings control panel. (This argument is optional.)

2. Insert the formula on the report.
    
3. Export the report to MS Excel.

MS Excel, CR, Crystal Reports, export, Decimals, Thousand separators , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020