Excel multiple IF statements or macro's to solve this problem?

Question

I'm trying to make multiple IF statements in Excel help my club convert event registrations into a template which can import into a scoring program.

The registration export places 3 attributes into a single cell. I need a way to separate those 3 attributes into 3 difference cells for the import template. Each attribute has 4-5 possibilities. I've been trying IF(ISNUMBER(SEARCH which let's me identify 1 attribute being present.

=IF(ISNUMBER(SEARCH("Production",G12,1)),"Production","")`

Desired outcome in red text:

Answer 1

Welcome to SO. Maybe this will help:

=IF(ISNUMBER(FIND(B$2,$A3)),TRIM(LEFT((SUBSTITUTE(MID($A3,FIND(":",$A3,FIND(B$2,$A3))+2,255),",",REPT(" ",255))),255)),"")

Result:

Answer 2

Please refer to above Ron Rosefield's layout table.

Here's another FILTERXML formula but in shorter version.

In C6, formula copied across and down :

=IFERROR(FILTERXML("<a "&SUBSTITUTE(SUBSTITUTE($A6,":","='"),",","' ")&"'/>","a/@"&C$5),"")

Answer 3

Given your data, here is one way to handle it if you have Excel 2013+ with the FILTERXML function.

• Create an XML dividing on both the comma and the colon.
• This XML will have nodes equal to each entry in your original data.
• The value of any particular attribute will be the value of the following-sibling
• An xPath will then extract the appropriate attribute value.

---

C6: =IFERROR(FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE($A6,":",","),",","</s><s>") & "</s></t>","//s[contains(.,'"&C$5&"')]/following-sibling::*[1]"),"")

Fill across and down as needed. Other attributes can be added to the header row.