Search code examples
if-statementlambdagoogle-sheets-formulasequenceflatten

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

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.

Solution

  • try:

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