Excel: mapped XML data and the "number stored as text" error

Question

A colleague of mine has an XML document which she opens in Excel 2007, using "Get External Data / From XML Data Import".

Excel maps the XML to columns automatically. However, some elements store numerical data, which Excel refuses to treat as a number:

<SOME_NUMBER>68.12</SOME_NUMBER>

Instead, the cell shows the "number stored as text" error.

Currently no XML Schema is attached to the document, and I would want to avoid creating one, if possible. What other options are there?

P.S.: Trying different regional settings was unsuccessful. Data always seems to be viewed as text.

Answer 1

Well you can just select the cells, right click, format cells, Change the format type to "Number"

EDIT

Using this test XML File

<NODES>
<A>
   <B>1.23</B>
   <C>2.34</C>
   <D>3.45</D>
   <SOME_NUMBER>ASDF</SOME_NUMBER>
</A>
<A>
   <B>1.23</B>
   <C>2.34</C>
   <D>3.45</D>
   <SOME_NUMBER>4.56</SOME_NUMBER>
</A>
<A>
   <B>1.23</B>
   <C>2.34</C>
   <D>3.45</D>
   <SOME_NUMBER>4.56</SOME_NUMBER>
</A>
</NODES>

All columns appear as numbers with the exception of the second two SOME_NUMBER's because of the text in the initial column

So I select those two cells, high-light the error box (exclamation sign) and choose the second option "Convert To Number"

The format cells thing seems to be a bit stupid in that it won't apply the formatting to a cell with an error on it, until you click into the cell

Answer 2

I believe that the cell formatting by default applies for the whole column, so perhaps in your example some instances of store text or number but in quotas like "1.5" so the default text formatting.

When I tested the XML Data Import it would format all text columns to text, and numeric columns to general

Answer 3

I feel your pain, I've had the same problem for years. This is the only solution I've came up with.

Function ConvertText2Num(RangeToConvert As Range)
    RangeToConvert.ClearFormats
    RangeToConvert.TextToColumns
End Function