title: Change x:y to x ↵ y
body: <p>I have the following problem: In my very large excel spreadsheet, I have a few rows that look like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Memo: IBRD</th>
  <th></th>
  <th>0</th>
  <th>0</th>
  <th>0</th>
  <th>0</th>
  <th>0</th>
  <th>0</th>
  <th>0</th>
  <th>0</th>
  <th>0</th>
  <th>0</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>IDA</td>
  <td></td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>and a few others that look like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Memo:</th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>IBRD</td>
  <td></td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  </tr>
  <tr>
  <td>IDA</td>
  <td></td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want them to be uniform and change all those that exhibit the first format to the SECOND one (where Memo: empty; and the next row is IBRD with the data entries).</p>
  <p>I have found the following VBA code that might do the trick but I am not 100% how to apply this to my problem here.</p>
  <pre><code>Sub splitOneCellIntoMore()
  Dim R As Range
  Dim I As Range
  Dim O As Range
  wTitle = &quot;splitOneCellIntoMoreCells&quot;
  Set I = Application.Selection.Range(&quot;B&quot;)
  Set I = Application.InputBox(&quot;Select the Cell B1 that contains the text you want to split:&quot;, wTitle, I.Address, Type:=8)
  Set O = Application.InputBox(&quot;Select destination cell you want to paste your split data:&quot;, wTitle, Type:=8)
  A = VBA.Split(I.Value, &quot;:&quot;)
  O.Resize(UBound(A) - LBound(A) + 1).Value = Application.Transpose(A)
  </code></pre>
  <p>End Sub</p>
  <p>(I took the code from: <a href="https://www.excelhow.net/split-one-cell-into-two-or-more-cells.html" rel="nofollow noreferrer">https://www.excelhow.net/split-one-cell-into-two-or-more-cells.html</a>)</p>
  <p>This is the table I am using.</p>
  <pre><code>| DEBT OUTSTANDING(LDOD)                                              |  |   |   | 36 | 330 | 405 | 412 | 527 |
  | ------------------------------------------------------------------- |--|---|---|---|---|---|---|---|
  | Public and publicly e;uaranteed                                     |  |   |   | 36 | 330 | 405 | 412 | 506 |
  | Official creditors                                                  |  |   |   | 2  | 305 | 376 | 385 | 480 |
  | Multilateral                                                        |  |   |   | 0  | 115 | 151 | 164 | 242 |
  | Concessional                                                      I |  |   |   | 0  | 110 | 142 | 155 | 232 |
  | Bilateral                                                           |  |   |   | 2  | 190 | 225 | 221 | 238 |
  | Concessional                                                        |  |   |   | 2  | 151 | 189 | 190 | 212 |
  | Private creditors                                                   |  |   |   | 34 | 25  | 29  | 27  | 26  |
  | Bonds                                                               |  |   |   | 0  | 0   | 0   | 0   | 0   |
  | Commercial banks                                                    |  |   |   | 0  | 0   | 0   | 0   | 0   |
  | Other private                                                       |  |   |   | 34 | 25  | 29  | 27  | 26  |
  | Privatenone;uaranteed                                               |  |   |   | 0  | 0   | 0   | 0   | 21  |
  | Bonds                                                               |  |   |   | 0  | 0   | 0   | 0   | 0   |
  | Commercial banks and other                                          |  |   |   | 0  | 0   | 0   | 0   | 21  |
  | Memo: IBRD                                                          |  | 0 | 0 | 0  | 0   | 0   | 0   | 0   |
  |                                                                     |  |   |   |    |     |     |     |     |
  | IDA                                                                 |  | 0 | 0 | 0  | 109 | 137 | 148 | 220 |
  | DISBURSEMENTS                                                       |  |   |   | 33 | 70  | 90  | 52  | 89  |
  | Public and publicly e;uaranteed                                     |  |   |   | 33 | 70  | 90  | 52  | 89  |
  </code></pre>