Sheets transpose and split comma-separated list with repeat

0
votes

I need the formula to transpose and split a comma-separated list and repeat it by a value in another cell.

See my sheet:

https://docs.google.com/spreadsheets/d/1AuBOdGhQWaXKSKghSzS7wcwQpiAnzZ9rskNUvptPbEQ/edit?usp=sharing

So far I have the following formula to transpose and split but it needs to repeat:

=TRANSPOSE(SPLIT(A1,","))
1
google-sheets

1 Answers

0
votes

Try this one:

=TRANSPOSE(SPLIT(REPT(A1 & ",",B1),",",false,true))

Where B1 is a range reference to the cell with the number of repetitions. The REPT function repeats a string a set number of times. A1 & "," adds an extra comma so that you can split the resulting string. The last 'true' parameter removes empty strings from the result so that last trailing comma won't affect your result.