title: Excel Separate Pipe Delimited String into separate columns - where value starts with A, B, C but values in string are not cronological
body: <p>I have an excel sheet, which has values from a database.  One cell has responses from a multiple choice question which is pipe delimited.  eg a user could ask what colour cars have you had?  With options such as:</p>
  
  <pre><code>A. Black
  B. White
  C. Red
  D. Yellow
  </code></pre>
  
  <p>So a user can respond with A, C and D.  these values are stored in one cell as "A. Black|C. Red|D. Yellow"  I want to separate each of these values in a A column, B column, C column and D column.  </p>
  
  <p>I tried using the Text to Column feature but this does know that the A column should only contain As.</p>
  
  <p>I thin I need to add a formula to each column which looks for the "A.", "B.", "C." or "D." and then finds the next available pipe character.  I think I need a substring of some sort maybe.  Something like this maybe:</p>
  
  <pre><code>=LEFT(C2,LEN(C2)-FIND("A.",C2))
  </code></pre>
  
  <p>But I don't know how to find the next occurrence of the pipe symbol - any ideas?  Is there a nextIndexOf function in excel maybe?</p>
  
  <p>Many thanks in advance </p>