title: How to split a column in Google Sheets into a seperate column every time there is a blank cell
body: <p>So what I am trying to do is take a column that is formatted like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Column</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>[text]</td>
  </tr>
  <tr>
  <td>$*****</td>
  </tr>
  <tr>
  <td>0-10%</td>
  </tr>
  <tr>
  <td>&gt;5</td>
  </tr>
  <tr>
  <td></td>
  </tr>
  <tr>
  <td>text</td>
  </tr>
  <tr>
  <td>$*****</td>
  </tr>
  <tr>
  <td>10-20%</td>
  </tr>
  <tr>
  <td>3</td>
  </tr>
  <tr>
  <td></td>
  </tr>
  <tr>
  <td>text</td>
  </tr>
  <tr>
  <td>$*****</td>
  </tr>
  <tr>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>and write a formula to separate the data into a new column every time there is a space (with the columns ending with the spaces) like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th></th>
  <th></th>
  <th></th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>[text]</td>
  <td>text</td>
  <td>text</td>
  </tr>
  <tr>
  <td>$*****</td>
  <td>$*******</td>
  <td>$******</td>
  </tr>
  <tr>
  <td>0-10%</td>
  <td>10-20%</td>
  <td></td>
  </tr>
  <tr>
  <td>&gt;5</td>
  <td>3</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Note that the first entry can be a string surrounded by brackets or not, second entry is a number, third entry is a string in that format or blank, and fourth entry is a a string of 1 digit or &quot;&gt;5&quot;. Most groups have all 4 entries, but some only have the first two.</p>
  <p>I have built a formula that works fine when all groups are the same length, but I need to adapt it so that the next column begins after the space, rather than at the 4th cell every time, otherwise everything starts wrapping around weird. If someone knows how to automatically add empty cells to the &quot;short&quot; groups , that would also fix the problem.</p>
  <pre><code>=Transpose(ArrayFormula(IFERROR(vlookup((TRANSPOSE(ROW(INDIRECT(&quot;a1:a&quot;&amp;ROUNDUP(COUNTA($B:$B)/6)))-1)*6 + ROW(INDIRECT(&quot;b1:b&quot;&amp;6)),{ROW($B:$B),$B:$B},2,))))
  </code></pre>
  <p>Thanks for trying to help!</p>