Excel: XML data displayed and error "number stored as text"

Question

My colleague has an XML document that she opens in Excel 2007 using "Get External Data / From Importing XML Data".

Excel automatically matches XML columns. However, some elements store numerical data, which Excel refuses to consider as a number:

<SOME_NUMBER>68.12</SOME_NUMBER>

Instead, the error "number saved as text" is displayed in the cell.

The XML schema is currently not tied to a document, and I would like to avoid creating one if possible. What other options are there?

PS: Failed to perform various regional settings. Data always seems to be treated as text.

Answer 1

() , :

: " XML- ", . XML-, Excel. - - .

, ( ): XML Excel

Answer 2

, , , , ""

XML

<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>

, SOME_NUMBER,

, , ( ) " "

, ,

Answer 3

, , , , , "1,5", .

XML-, , -

Answer 4

I feel your pain, I have had the same problem for years. This is the only solution I have come across.

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