How to duplicate strings and attach the numeric suffix using formulas on Google Spreadsheet?

Question

I have strings with each line in one cell. The number after # means the number of items. Is it possible on Google Spreadsheet to write a formula that allows you to process like B cell if you input strings in A cell like the attached image? I tried to combine formulas such as JOIN, SPLIT, ARRAYFORMULA, and SUBSTITUTE, but failed. Should I learn the query?

Answer 1

sure:

=INDEX(TEXTJOIN(CHAR(10), 1, IF(""=SPLIT(REPT(
 INDEX(SPLIT(FLATTEN(SPLIT(A1, CHAR(10))), "#"),,1)&"-×", 
 INDEX(SPLIT(FLATTEN(SPLIT(A1, CHAR(10))), "#"),,2)), "×"),,SPLIT(REPT(
 INDEX(SPLIT(FLATTEN(SPLIT(A1, CHAR(10))), "#"),,1)&"-×", 
 INDEX(SPLIT(FLATTEN(SPLIT(A1, CHAR(10))), "#"),,2)), "×")&
 TEXT(SEQUENCE(1, 1000), "00"))))

---

or arrayformula:

=INDEX(SUBSTITUTE(SUBSTITUTE(TRIM(IFNA(VLOOKUP(ROW(A1:A5), SPLIT(TRIM(FLATTEN(
 QUERY(SUBSTITUTE(QUERY(IFERROR(SPLIT(FLATTEN(IF(""=IFERROR(SPLIT(REPT(
 INDEX(SPLIT(FLATTEN(SPLIT(A1:A5, CHAR(10))), "#"),,1)&"-×", 
 INDEX(SPLIT(FLATTEN(SPLIT(A1:A5, CHAR(10))), "#"),,2)), "×")),,SPLIT(REPT(
 INDEX(SPLIT(FLATTEN(ROW(A1:A5)&"♠♦"&SPLIT(A1:A5, CHAR(10))), "#"),,1)&"-×", 
 INDEX(SPLIT(FLATTEN(SPLIT(A1:A5, CHAR(10))), "#"),,2)), "×")&
 TEXT(SEQUENCE(1, 100), "00"))), "♦")), 
 "select max(Col2) where Col1 is not null group by Col2 pivot Col1"), 
 " ", CHAR(13)),,9^9))), "♠"), 2, 0))), " ", CHAR(10)), CHAR(13), " "))