Repeating sequence with leading zeros to make all values same length in Google Sheets-CodePudding

The following code gives a repeating sequence of specified numbers from 1 to 12, repeating it 12 times. However, the numbers generated have a different length (1,2,3...10,11,12). How can the formula be modified so that leading zeros are added in order to make all numbers the same length?

transpose(split(REPT(concat(JOIN(",",SEQUENCE(1,12)),","),ROUNDDOWN(ROWS(A1:A)/15)),",",true))

For example, in this example there are numbers with length = 2 and then the desired sequence would be 01,02...10,11,12. However if the sequence was up to 3 or more digits, for example:

...(",",SEQUENCE(1,150)),",")...

Then a desired sequence would be 001...010...150. A sequence going up to 4 would be 0001...1500 etc.

CodePudding user response：

try:

=INDEX(TEXT(FLATTEN(MAKEARRAY(25, 12, LAMBDA(x, y, y))), "00"))

CodePudding user response：

BASE formula has min_length argument. You can use it to set the LENgth of your sequences. It's also easy to create the sequence without TRANSPOSE/SPLIT/JOIN/SPLIT with just IF/FLATTEN. For eg, To create sequence of 12, 25 times,

=ARRAYFORMULA(FLATTEN(IF(SEQUENCE(25),BASE(SEQUENCE(1,12),10,LEN(12)))))