CODEWITHSUNDEEP
rubyxmlexcel
Tintin81
Tintin81

Reputation: 10225

How to get Excel XML to keep leading zeros in strings?

I am trying to generate an XML file through Ruby:

<ss:Cell>
    <ss:Data ss:Type="String">0000023</ss:Data>
</ss:Cell>

However my invoice numbers all display in Excel with a green (and ugly) comment triangle. When I then click the cell, the leading zeros are stripped and the comment disappears. This is not what I want since the zeros are part of the invoice numbers. How can I prevent Excel from doing that?

The strings are coming from a MySQL database type varchar255 field by the way. So they have been strings from the very beginning and shouldn't be considered numbers by Excel anyway.

Upvotes: 1

Views: 3072

Answers (2)

Max B
Max B

Reputation: 375

You need to use the Data->Get & Transform->New Query function to import the XML and adjust the data type to Text in the Query editor before loading it. See

https://support.microsoft.com/en-us/office/keeping-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7

for more details.

Upvotes: 0

trincot
trincot

Reputation: 351369

This is an option in Excel. It has nothing to do with how you generate the content (from MySql, with Ruby, in XML format, ...).

The steps to turn it on (or off) are described in the Office support pages:

Use error checking to convert numbers that are stored as text to numbers

With Error Checking turned on, numbers that are entered in cells that are formatted as text are marked with an error indicator green triangle.

To turn on Error Checking, do the following:

  • Click the Microsoft Office Button, and then click Excel Options.

  • Click the Formulas category.

  • Under Error Checking, make sure that the Enable background error checking check box is selected.

  • Under Error checking rules, make sure that the Numbers formatted as text or preceded by an apostrophe check box is selected.

  • Click OK.

options window

Disabling the second option should do the trick, but you might want to disable the first as well.

Upvotes: 2

Related Questions