Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there a way to break an Excel column containing XML into several columns like this:

BEFORE

| Apples | Pears | XML                              |
| ------ | ----- | ---------------------------------|
| 35     | 18    | <Plums>34</Plums><Figs>19</Figs> |
| 86     | 55    | <Plums>12</Plums><Figs>62</Figs> |
| 99     | 12    | <Plums>18</Plums><Figs>23</Figs> |

AFTER

| Apples | Pears | Plums | Figs |
| ------ | ----- | ----- | ---- |
| 35     | 18    | 34    | 19   |
| 86     | 55    | 12    | 62   |
| 99     | 12    | 18    | 23   |

The original table comes from an SQL Server that stores XML in a text column. If the XML column contained comma-separated values, I'd tell Excel to do Text to Columns. Is there a similar feature for XML?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

In addition to Text manipulation in Excel there are two other options.

  1. You could use VBA to parse the XML. You could either use string functions to look for tags or actually use XML parsers to walk the content (see this question on SO for more info).

  2. You can use the XML Source feature in Excel (see here for an overview). The easiest way to use this is to load in an XML file. Excel will automatically try and create an XML map for the loaded data and load it into a table. For it to really work well though you need to create a schema.

For your data I added a root element and saved this in a file:

<Fruits>
    <Plums>34</Plums><Figs>19</Figs>
    <Plums>12</Plums><Figs>62</Figs>
    <Plums>18</Plums><Figs>23</Figs>
</Fruits>

Loaded into Excel this creates the following table:

enter image description here

share|improve this answer

Given that the stuff you don't want is the same size (at least in the example) use text to columns with 'Fixed Width'

share|improve this answer
    
Thank you pnuts and James. Your solutions might work, but they are based on treating the XML code as a string, not as a logical structure. Since Excel is XML-aware, I thought there was a more elegant solution. And no, constant width is not guaranteed. –  Mihai Nagy Apr 30 '13 at 18:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.