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

user_12876028 0
Viewed 77

enter image description here
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?

textjoin google-sheets-formula flatten google-sheets split
2 Answers

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

enter image description here

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

enter image description here

user_56326290

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

ADDENDUM (after comments):

The OP realized that I'd left off the sequencing of numbers. This definitely made things quite a bit trickier. However, I was able to write an array formula that should work.

Again, supposing that the raw data runs A2:A, clear B2:B and then place the following into cell B2:

=ArrayFormula(REGEXREPLACE(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(REGEXREPLACE(IFERROR(VLOOKUP(SEQUENCE(ROWS(A2:A))&"^"&SEQUENCE(1,COLUMNS(SPLIT(A2:A,CHAR(10))))&"*",TRANSPOSE(QUERY(TRANSPOSE(REGEXREPLACE(SPLIT(QUERY(FLATTEN(FILTER(IFERROR(SEQUENCE(ROWS(A2:A))&"^"&SEQUENCE(1,COLUMNS(SPLIT(A2:A,CHAR(10))))&"^"&REPT(REGEXEXTRACT(SPLIT(A2:A,CHAR(10)),"[^#))]+")&"~",REGEXEXTRACT(SPLIT(A2:A,CHAR(10)),"#(\d+)")*1)),A2:A<>"")),"Select * WHERE Col1 Is Not Null"),"~")&TEXT(SEQUENCE(1,MAX(IFERROR(SPLIT(REGEXREPLACE(A2:A,"[^#\d]",""),"#",1)))),"00")&"~","^\d+~$","")),,MAX(IFERROR(SPLIT(REGEXREPLACE(A2:A,"[^#\d]",""),"#",1))))),1,FALSE)),"^\d+\^\d+\^","")),,COLUMNS(SPLIT(A2:A,CHAR(10))))),"~\s*$",""),"~\s*",CHAR(10)))

Is it long? Sure. Does it work? Yes, it should. And being an array formula, it actually saves processing and eliminates the need to drag the formula down as new rows of data are added in A2:A.

user_139032830
Related