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

user_12243215 0
Viewed 89

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:

https://imgur.com/a/trc1fmU

multiple-arguments excel-formula excel nested
3 Answers

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:

enter image description here

user_106719260

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),"")
user_121251810

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.

enter image description here

user_28729220
Related