Labels starting with + or – are exported in Excel file with an extra prefix (single quote)

Answered
  • Version: Pivot Table for JSON data sources 2.6.11 (Jan 02, 2019)
  • Browser: Chrome 71.0.3578.98 on MacOS 10.14.1
  • Link to reproducehttp://jsfiddle.net/udjgy4ab/
  • Steps to reproduce:
    • Display pivot table
    • See that 2 row labels start with “+” and “-” respectively for the column “Sign
    • Click on Toolbar > “Export” > “To Excel”
    • Open the exported file (e.g. pivot.xlsx) with Excel
    • See that the corresponding row labels start with “‘+” and “‘-“respectively for the column “Sign” (extra single quote)
  • Observed behavior:
    • In the exported Excel file, the labels for the rows originally starting with “+” or “-” now start with an extra ‘ (single quote).
  • Expected behavior:
    • In the exported Excel file, the labels should be the same as the original label in the pivot table, including when they start with “” or “+
  • Notes:
    • In Excel, we can usually add “” (single quote) in a cell’s formula to indicate it should be interpreted as a string (i.e. “‘0001” will create a text cell displayed as “0001” and not just “1“).
    • This behavior is OK for labels like “0001” that are displayed correctly in Excel (without a single quote).
    • However, selecting the cell that contains “‘+‘, for example, and applying the formula will also result in the cell being converted to text while removing the extra single quote.
  • Workaround:
    • As noted above, the user could select the cells starting with an extra single quote, applying the formula (hit Enter in the formula box) and the extra single quote would be removed, while re-converting the cell to a text cell (which is already the case). This fix however requires human interaction.

1 answer

Public
Dmytro Zvazhii Flexmonster January 16, 2019

Hello,
Thank you for the detailed specification.
We will add the necessary fix to the minor release with ETA Feb 25.
Regards,
Dmytro

Please login or Register to Submit Answer