Split Excel column with XML inside

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

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

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

Related