I received a number of music CDs from a friend, all burned as mp3s on a DVD-R. There was a spreadsheet with the disc outlining the artists/albums on each DVD, but the format is of little use to me and I wish to convert it to a more usable format.
Existing (in one cell):
Artist1 - Album1, Album2; Artist2 - Album1; Artist3 - Album1, Album2, Album3; etc.
I can use Text to Column and break at the semicolon (
and the comma (,), and then use Transpose to switch from row to column, which gives this:
Artist1 - Album1
Album2
Artist2 - Album1
Artist3 - Album1
Album2
Album3
The issue is that in the original spreadsheet, all rows are filled, so using Transpose would overwrite existing data (which I obviously don’t want). Is there a way to automatically open new rows as required when using Transpose, so existing data doesn’t get overwritten? I’m okay using macros, if that’s what it takes. Any guidance/assistance greatly appreciated.
CALC info:
Version: 7.6.0.3 (X86_64) / LibreOffice Community
Build ID: 69edd8b8ebc41d00b4de3915dc82f8f0fc3b6265
CPU threads: 16; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-CA (en_CA); UI: en-US
Calc: CL threaded