Parse & extract multiple recurring values from a string

[AaronLyles]

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.

[Leith Ross]

Hello AaronLyles,

Welcome to the Forum!

When asking for help with parsing questions, it is important to include an example of the data . The more examples you have the better the results. You should also include before and after results along with any comments. If you have a workbook and/or text file you can post, please do so.