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

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?

CodePudding user response：

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"))))

CodePudding user response：

Suppose that you want to do this for multiple cells of data in the range A2:A. Clear B2:B and place the following formula in B2:

=ArrayFormula(IF(A2:A="",,IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(REPT(REGEXEXTRACT(SPLIT(A2:A,CHAR(10)),"[^#))] ")&"~",REGEXEXTRACT(SPLIT(A2:A,CHAR(10)),"#(\d )")*1)),,COLUMNS(SPLIT(A2:A,CHAR(10)))))),"~ ","~"),"~",CHAR(10))))))

This formula should produce all results for all rows where A2:A contains data.

If you only want to process the one cell (say, A2), you can use this version in, say, B2:

=ArrayFormula(IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(REPT(REGEXEXTRACT(SPLIT(A2,CHAR(10)),"[^#))] ")&"~",REGEXEXTRACT(SPLIT(A2,CHAR(10)),"#(\d )")*1)),,COLUMNS(SPLIT(A2:A,CHAR(10)))))),"~ ","~"),"~",CHAR(10)))))