IT Developer

Lia Nungaray

<div>
FILTERXML will return a vertical array of results. You may retrieve individual elements using the INDEX function. So if you don't have TEXTJOIN and dynamic arrays, you can use this brute force formula to retrieve up to three elements. If you might need more, feel free to extend it by replicating the "middle" line and updating the INDEX parameters 1, 2, 3 as needed.<br><br>The following formula is working in Windows Excel 2013.<br><pre><code class="code-1 nolinks" id="code-20-43318220-1"><span>=IFERROR(INDEX(FILTERXML($A2,"//OptionName"),1),"") &amp; </span>
<span>&nbsp; IFERROR(", " &amp; INDEX(FILTERXML($A2,"//OptionName"),2),"") &amp; </span>
<span>&nbsp; IFERROR(", " &amp; INDEX(FILTERXML($A2,"//OptionName"),3),"")</span>
</code></pre>
</div>


<div>
<div class="content wysiwyg-content fr-view">
I have the following text stored in one cell in Excel:<br><br>&lt;ns1:FieldOptionList xmlns:ns1="http://alerewellnessportal.com/xsds/UserManagement.xsd"&gt; &lt;OptionList&gt; &lt;OptionName&gt;Employee&lt;/OptionName&gt; &lt;OptionValue&gt;25&lt;/OptionValue&gt; &lt;SPOptionName&gt;Empleado&lt;/SPOptionName&gt; &lt;SPOptionValue&gt;25&lt;/SPOptionValue&gt; &lt;/OptionList&gt;&lt;OptionList&gt; &lt;OptionName&gt;Spouse&lt;/OptionName&gt; &lt;OptionValue&gt;11&lt;/OptionValue&gt; &lt;SPOptionName&gt;Cónyuge&lt;/SPOptionName&gt; &lt;SPOptionValue&gt;11&lt;/SPOptionValue&gt; &lt;/OptionList&gt; &lt;/ns1:FieldOptionList&gt; <br><br>I need to extract whatever text is between &lt;OptionName&gt; and &lt;/OptionName&gt; and separate each item with a comma. So, for the above text, I would get:<br><br>Employee, Spouse<br><br>Thanks,
</div>
</div>



I have the following text stored in one cell in Excel:
<ns1:FieldOptionList xmlns:ns1="http://alerewellnessportal.com/xsds/UserManagement.xsd"> <OptionList> <OptionName>Employee</OptionName> <OptionValue>25</OptionValue> <SPOptionName>Empleado</SPOptionName> <SPOptionValue>25</SPOptionValue> </OptionList><OptionList> <OptionName>Spouse</OptionName> <OptionValue>11</OptionValue> <SPOptionName>Cónyuge</SPOptionName> <SPOptionValue>11</SPOptionValue> </OptionList> </ns1:FieldOptionList>
I need to extract whatever text is between <OptionName> and </OptionName> and separate each item with a comma. So, for the above text, I would get:
Employee, Spouse
Thanks,

How to extract data from Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.