title: How do you extract a subarray from an array in a worksheet function?
body: <p>Is there some way of getting an array in Excel of a smaller size than a starting array in a cell worksheet function?</p>
  
  <p>So if I had:</p>
  
  <pre><code>{23, "", 34, 46, "", "16"}
  </code></pre>
  
  <p>I'd end up with:</p>
  
  <pre><code>{23, 34, 46, 16}
  </code></pre>
  
  <p>which I could then manipulate with some other function.</p>
  
  <p><strong>Conclusion:</strong> If I was to do a lot of these I would definitely use jtolle's UDF comb solution.  The formula that PPC uses is close, but diving in and testing, I found it gives errors in the empty slots, misses the first value, and there is an easier way to get the row numbers, so here is my final solution:</p>
  
  <pre><code>=IFERROR(INDEX($A$1:$A$6, SMALL(IF(($A$1:$A$6&lt;&gt;""),ROW($A$1:$A$6)),ROW(1:6))),"")
  </code></pre>
  
  <p>Which must be entered as an array formula (CTRL-SHIFT-ENTER).  If being displayed then it must be entered in at least an area as big as the resultset to show all results.</p>