0

How can I convert a column with variable data like this:

|   <h2>TITLE 1</h2>            |
|   <input ...>Item A</input>   |
|   <input ...>Item B</input>   |
|   <h2>TITLE 2</h2>            |
|   <input ...>Item F</input>   |
|   <input ...>Item G</input>   |
|   <input ...>Item H</input>   |

             ...

|   <h2>TITLE 45</h2>           |
|   <input ...>Item ZZA</input> |
|   <input ...>Item ZZB</input> |
|   <input ...>Item ZZC</input> |

To a table like this?

|   <h2>TITLE 1</h2>    |   <input ...>Item A</input>   |
|   <h2>TITLE 1</h2>    |   <input ...>Item B</input>   |
|   <h2>TITLE 2</h2>    |   <input ...>Item F</input>   |
|   <h2>TITLE 2</h2>    |   <input ...>Item G</input>   |
|   <h2>TITLE 2</h2>    |   <input ...>Item H</input>   |

                       ...

|   <h2>TITLE 45</h2>   |   <input ...>Item ZZA</input> |
|   <h2>TITLE 45</h2>   |   <input ...>Item ZZB</input> |
|   <h2>TITLE 45</h2>   |   <input ...>Item ZZC</input> |
  • The number of "Item" rows between the "TITLE" rows is variable.
  • Titles always begin with <h2> and end with </h2>
  • Items always begin and end with <input ... > and with with </input>

I'm a pretty advanced user, but I can't figure this one out. Array formula? Maybe it needs to be a scripted solution? I can read and modify scripts, but not really write it from scratch :)

Here's a sample sheet.

  • Updated my question. Thanks! docs.google.com/spreadsheets/d/… – Kenny Sep 1 '18 at 23:40
  • what is the logic in your actual data? titles always have number and items ends always with a letter? – user0 Sep 3 '18 at 22:47
  • Good question @user0. I updated my question to include the logic. Fortunately, each title begins with <h2> and each item begins with <input (stuff goes here) ... > – Kenny Sep 4 '18 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.