Search code examples
excelxmlleading-zero

keep leading zeros and decimals when exporting XML file from Excel

I have imported an XML file into Excel without any schema/map. Excel has created a map based on the data. I have remapped the items and formatted the cells as they should appear.

Leading zeros example:

enter image description here

I use this formatting because if I use Text, it does not work when I import new date from another XML file. The leading zeros are stripped down.

Decimals example:

enter image description here

Same reason for the formatting as above.

The problem: When I export the data to an XML file all this formatting is stripped down. Example: enter image description here

Is there a way to retain that formatting when exporting from Excel? I checked other topics connected with this issue but there is no definitive answer or solution.

Solution

  • The suggestion by @andrewb actually worked.

    You just need to import the XML into excel and re-map the XML schema to different cells where you can use the TEXT function:

    =TEXT([@any_cell],"0000000000")

    To refer to the cell with the data and format it the way you want. It does keep leading zeros and decimals when you export it back to XML format.

    I actually created 2 schema maps - one for importing the data and one for exporting the data with formatting. Excel asks when you save which map to use.