Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    XML numbers formatted as text (2003)

    I am importing XML as a list into an Excel worksheet. The XML file contains numbers as well as text, but when I import the data, the numbers are formatted as text (even though text and numbers are never mixed in the same cell). The actual Excel popup message says that "the numbers are formatted as text or preceded by an apostrophe". I can convert them to numbers by selecting a block of contiguous cells and using Paste Special (as per the Help), but when I do so, it places a 0 in any cells that were originally empty. This is really ugly. How can I have the numbers come in as numbers without having to go through this rigmarole?

    Thanks for your help.

    Roger Shuttleworth
    London, Canada

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML numbers formatted as text (2003)

    It is simpler to select the data and choose Data, text-to-columns, finish.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    May 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML numbers formatted as text (2003)

    Thanks, Jan. That makes it easier. But my ideal solution would be to have the numbers come in as numbers from the XML. I should explain further what I am doing:

    I start with a source XML file that contains both text and numbers that are actually time in seconds. I run an XSL transform on it to select and rearrange data, and also to convert the seconds to hours. The XSL includes the xsd:format-number() function, so they really *are* numbers. However, there is no associated schema that specifies a data type. When I open my resultant file in Excel, it regards the numbers as text and flags them with the little green triangle and a note that "they are formatted as text or preceded by an apostrophe". Why doesn't it just treat them as the numbers that they are?

    By the way, I also notice that when I run the Text to Columns command, it tells me that my data is delimited, which may give a clue to why this is happening.

    Regards,
    Roger

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML numbers formatted as text (2003)

    It shouldn't be too hard to create a schema yourself and use that in the import.

    The fact that text to columns suggest delimited is no real indication of the problem.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML numbers formatted as text (2003)

    You can always write VBA code to parse the file and store the data in any format you want.
    Legare Coleman

  6. #6
    New Lounger
    Join Date
    May 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML numbers formatted as text (2003)

    Hello Legare

    I solved the problem by writing a schema and attaching it to the Excel template. I'm just mystified why Excel would not treat the numbers as numbers in the first place. Thanks for your help, and Jan's too.

    Roger

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML numbers formatted as text (2003)

    The problem is that when there is no schema, Excel has to try and come up with one. Obviously something in the data made Excel assume they weren't numbers.
    Could you perhaps attach a sample of the xml file (maybe change it a bit to remove confidential information)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML numbers formatted as text (2003)

    It is most likely because the data contains some character before or after the number (a non-breaking space for example) which causes Excel to think that the number is not a number. This is common for data taken from the internet.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •