title: Split string using Excel
body: <p>I have a string with the following format:</p>
  
  <pre><code>StockCode Country Date Price equity
  </code></pre>
  
  <p>for example:</p>
  
  <ul>
  <li><code>1 hk 10/31/12 C70.5 equity</code></li>
  <li><code>101 hk 11/21/13 P63 equity</code></li>
  <li><code>388 hk 10/17/12 P100 equity</code></li>
  </ul>
  
  <p>I can extract Date by this Excel Command: </p>
  
  <pre><code>LEFT(RIGHT(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)),
  LEN(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)))-FIND(" ",
  RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)))),FIND(" ",
  RIGHT(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)),
  LEN(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)))-FIND(" ",
  RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1))))))
  </code></pre>
  
  <p>(line breaks for readbility)</p>
  
  <p>result: <code>10/31/12</code></p>
  
  <p>Anyone has a better solution for this?</p>
  
  <p>here are some of my codes:</p>
  
  <ul>
  <li><p>how to get "C" and "P":</p>
  
  <pre><code>TRIM(MID(A1,IFERROR(FIND("P",A1),FIND("C",A1)),1))
  </code></pre></li>
  <li><p>how to get the strike price:</p>
  
  <pre><code>TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)-IFERROR(FIND("P",A1),
  FIND("C",A1))),"equity",""))
  </code></pre></li>
  </ul>