Thread: Parse & extract multiple recurring values from a string

Hello and thanks in advance.

I have searched and searched and can't seem to find a simple solution to this problem. All help is appreciated.

I have a column where each cell contains data that seems to be xml. Each row's cell data looks similar to this:

<ItemSpecifics><NameValueList><Name><![CDATA[Cost(#.##)*]]></Name><Value><![CDATA[34.34]]></Value><Source>ItemSpecific</Source></NameValueList><NameValueList><Name><![CDATA[List price (#.##)*]]></Name><Value><![CDATA[56.66]]></Value><Source>ItemSpecific</Source></NameValueList><NameValueList><Name><![CDATA[Package quantity (#)]]></Name><Value><![CDATA[2]]></Value><Source>ItemSpecific</Source></NameValueList><NameValueList><Name><![CDATA[Promoted to front page (1|0)*]]></Name><Value><![CDATA[1]]></Value><Source>ItemSpecific</Source></NameValueList><NameValueList><Name><![CDATA[Comments (Disabled|Read/Write|Read)]]></Name><Value><![CDATA[Disabled]]></Value><Source>ItemSpecific</Source></NameValueList></ItemSpecifics>

What I want to do for each cell in each row of this column is find and extract the unknown string variable between each instance of:

<Name><![CDATA[

and

]]></Name>

and make make that a header name for a new column IF that column does not yet exist.

Then I want to find and extract the unknown variable string between:

<Value><![

and

]]></Value>

and place it under the header column in the same row as this cell's data.

So in this example the first header would be:

Cost(#.##)*

and the first value under that header would be:

34.34

Essentially I'm having a problem learning how to parse xml data in multiple cells and place it in their own columns and rows

Note: In my spreadsheet the first row already contains header names.

Note: the next row down would contain the same header names but have different values associated with them.

Note: I have no way of knowing how many header values will exist before I run the script.

Thankyou.