Is there a way to Split strings in a google sheet cell into multiple rows?

Question

Is there a way to Split strings in a google sheet cell into multiple rows?
Example:

Current:
Oranges       Drums        8       9
Mangos        Guitars
Grapes        Piano

Expected:
Oranges       Drums        8       9

Mangos        Guitars      8       9

Grapes        Piano        8       9

Thank you.

Answer 1

It seems the data is much more easier to process than what assumed based on the post.

Try this:

=query(query({transpose(split($A$1, char(10))),transpose(split($B$1, char(10)))}, "select Col1, Col2, '"&$C$1&"', '"&$D$1&"'"), "offset 1", 0)

Output:

Answer 2

This is an interesting question. I've met this problem before and I had a simple but not very elegant solution for this:

=if(C8<>"",C8,E7)

Simply - I check whether columns on my left are empty. If I find some values there, I copy contents, if these columns on left are empty I copy values from above.

This solution requires to copy down all the IF formulas.