Problem with exported XML data - Want to search in cells & extract strings that match list


New Member
May 28, 2015
<div>Hi there everyone,</div><div><br></div><div>I've been struggling with the following; might be a bit of a challenge. :oops:<div><br></div>I have an excel file that was exported from some XML application.</div><div>It contains expressions like those below. Each expression is in a separate cell in a single row.</div><div><br></div><div>Now, in a column, say in a second tab, I have a list/range of a couple hundred strings/attributes like <strong>Measurement_1</strong>, <strong>Ref_No </strong>and<strong> Maximum_Velocity</strong>. I need to search within each cell of this row, find every attribute from my list that appears in these cells and get them in the next row, each in a cell of its own.</div><div><br></div><div>Is there some function, regex or VBA script that can do that? I have searched extensively in the forums and elsewhere and failed to do it myself.</div><div><br></div><div>Ideally duplicates both within each cell and within each row should be ignored, so Row 2 should only have unique values (so <strong>Measurement_1</strong> would only appear once), but I can use the remove duplicates functionality if this is not feasible.</div><div><br></div><div>I don't know if this helps at all, but all of the required attributes are in double quote marks ("Example"), though not all of the quote marked are useful attributes. For example, "<strong>valueandunit</strong>" and <strong>" of"</strong> are useless to me and aren't in my list.</div><div><br></div><div><strong>Many thanks for your help!</strong></div><div><br></div><div>-------------<br><br></div><div><strong>Example row</strong></div><div><br></div><div>Cell A1:</div><div><ref attrid="Measurement_1" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Measurement_1" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=" - "></ref></ref></div><div><br></div><div>Cell B1:</div><div><ref attrid="Standard_Deviation" equalsign=" - " includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Validation_2" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=", "></ref></ref></div><div><br></div><div>C1:</div><div>Maximum  <ref attrid="Compatibility" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""> routing capacity and <ref attrid="Maximum_Velocity" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""> plunge stroke</ref></ref></div><div><br></div><div>D1:</div><div>"<ref attrid="Ref_No" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Unit" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=" "><ref attrid="Vector" equalsign="=" includeattrname="false" resolveto="value" separator=" (Dia)"><ref attrid="Object_Diameter" equalsign="=" includeattrname="false" resolveto="unit" separator=""><ref attrid="Thread_Size" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=","><ref attrid="Object_Length" equalsign="=" includeattrname="false" resolveto="value" separator=" "><ref attrid="Object_Length" equalsign="=" includeattrname="false" resolveto="value" separator=" of ">"</div><div><br></div><div>E1:</div><div>CONCATENATE("This ", LOWER(VAL("Device_Type")),F(AND(EXACT(VAL("Max_Temperature_Range"),("")),EXACT(VAL("Min_Temperature_Range"),"")),".",CONCATENATE("appeared to operate safely from a minimum temperature of ", VAL("Min_Temperature_Range"),UNIT("Min_Temperature_Range")," to a maximum temperature of ", VAL("Max_Temperature_Range"),UNIT("Max_Temperature_Range"),".")))</div><div><br></div><div><strong>The required output would look like this:</strong></div><div><br></div><div>Cell A2: Measurement_1</div><div>B2: Standard_Deviation</div><div>C2:Validation_2</div><div>D2:Compatibility</div><div>E2:Maximum_Velocity</div><div>F2:Ref_No</div><div>G2:Object_Diameter</div><div>H2:Object_Length<br>I2:Device_Type<br>J2:Max_Temperature_Range<br>K2:Min_Temperature_Range<br><br>-------------</div>

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back