title: Excel String Parsing
body: <p>I have a spreadsheet that uses a string convention to convey levels of detail about individual "cases."</p>
  
  <p>The convention is as follows:  There are 5 main categories for the data in the string. 1=CATEGORY, 2=TOOLS, 3=DOCUMENTATION, 4=PROCESS, 5=JOB AID.</p>
  
  <p>Each category can have multple sub categories broken down by 1a, 1b, 1c or 2a, 2b, 2c, etc for each 5 category.</p>
  
  <p>Main category is separated from sub category by ":" and sub catory is separated from sub cateogry by "," whereas sub catory is separated from a new Main category by a ";"</p>
  
  <p>an example of a string: "1:1i;2:2a;3:3a,3d,3l;4:4a"</p>
  
  <p>Here is a sample breakdown of category in values:</p>
  
  <pre><code>CATEGORY    1
  Incorrect:VG:QOC    1i
  
  TOOLS   2
  Macro:Used  2a
  
  DOCUMENTATION   3
  TAT:Missing 3a
  ROUTING:Missing 3d
  STORY:Missing Impact to Health  3i
  
  PROCESS 4
  CNX Checklist Not Used  4a
  </code></pre>
  
  <p>I want to be able to pull out in text form what main categories and sub categories were flagged.</p>
  
  <p>Example query:<br>
  Of all strings, how many times was main category 4 flagged?
  Of all 4 flagged, how many times was 4a flagged?
  What were all elements flagged on one "case" or string?</p>
  
  <p>I can build the strings, I just cannot parse them.  Please help... :)</p>