Link to home
Create AccountLog in
Avatar of Lia Nungaray
Lia NungarayFlag for United States of America

asked on

How to extract data from Excel

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,
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of byundt
byundt
Flag of United States of America image
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.

The following formula is working in Windows Excel 2013.
=IFERROR(INDEX(FILTERXML($A2,"//OptionName"),1),"") & 
  IFERROR(", " & INDEX(FILTERXML($A2,"//OptionName"),2),"") & 
  IFERROR(", " & INDEX(FILTERXML($A2,"//OptionName"),3),"")

Open in new window