I am using the WEBSERVICE function in Excel 2013 to download XML files into a cell for me dynamically.  Then I am using the FILTERXML function to parse these XML files automatically.  Everything works well, but on any numeric values which are greater than 1900 and less than 9999, Excel is spitting out the number of days between my number and 1/1/1900.  I was only able to find one article regarding this bug here: 'https://github.com/rubensayshi/gw2spidy/pull/116' and therefore am hoping there is an easy fix or I am doing something wrong since the rest of the world hasn't mentioned it.

An example XML string I am dealing with is below using WEBSERVICE, which I have located in cell A2.
<?xml version='1.0' encoding='UTF-8'?>
<blueprint id="33201" name="Scan Rangefinding Array II" productiontime="4500" productionmodifier="900" waste="10" >
<totalmaterials>
<material name="Number1" id="87" />
<material name="Number2" id="1902" />
<material name="Number3" id="186" />
</totalmaterials>
</blueprint>

My current solution for parsing the example above using FILTERXML:
=FILTERXML(A2,"//totalmaterials/material[2]/@id")

It should return the value 1902, but instead returns a value of 731 because of this problem.  What could I do to fix it please?

It is a pity that there is no easy fix for this issue.
It seems that this function tries to recognize dates in the queried XML data and it will converts XML-data before the data is output to a cell. Also we can't change the format of  Xpath argument, so we can't control the output.

All that I could think for it is to substitute the numbers between [1900,9999] to[1900.0-9999.0] ,append a .0 to the end of numbers .

Thank you for the reply.  That is what the other thread I linked suggested as well.  However, I could not find a way to do that dynamically.  My spreadsheet is using webservice/filterxml for over 500 different items coming from active web pages so I won't be able to just edit the text and append the necessary .0 to the numbers.  Since the numbers are corrupt after the FILTERXML function touches it, I can't extract the numbers in that manner either.

I did some looking around for any sort of flexible formula that would search the original XML string, find the problem numbers, and then append the .0 for me.  I could not find a solution though that would allow me to enter a range for excel to look for inside of string but the idea is promising.  Thanks for the input, I will try and tinker with a solution along those methods for now but hoping someone else has a fix they already came up with that they are willing to share.

Hi Mad58Max,

There is no formulas can do this dynamically, but I guess VBA code should be able to do that .You can post your question to the Excel for Developers forum:
http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

best regards,
wind Zhang

FYI - I was unable to find a solution to fix this bug without downloading a third party add-on for Excel or coding in VBA.  It would be nice to not have to do any of that stuff for what is obviously a bug in Excel 2013.  For my solution, I was lucky to have found a different XML page to grab that works around this bug for me, but hopefully MSFT will put this one on their to-do list for future patches.

Cheers,

Max

Hi Max,

Thanks for your valuable feedback. Since the FILTERXML function is a new feature of excel 2013,there are may still some deficiencies, I think Microsoft will continue improve it in the future.

Regards,
Wind