2

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

JvdV
  • 70,606
  • 8
  • 39
  • 70
Dbaus
  • 23
  • 3

3 Answers3

2

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

Justyna MK
  • 3,523
  • 3
  • 11
  • 25
  • 1
    Amazing!! I've read through this 10x and still amazed at what it's doing :) Thanks so much you've made my job for upcoming events sooooo much easier!! – Dbaus Oct 20 '19 at 00:17
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),"")
bosco_yip
  • 3,762
  • 2
  • 5
  • 10
1

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

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Can I only add one correct answer? both this and the other answer do what I want. The other one hurts my brain less to understand though :p – Dbaus Oct 20 '19 at 00:19
  • @Dbaus You can only accept one answer. But you can upvote other responses. – Ron Rosenfeld Oct 20 '19 at 01:23