Google Sheets – How to Generate Custom Sequence

concatenateformulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I would like to create a column with a custom sequence: A-Z,a-z,0-9. (Essentially the Base64 character set with the last two characters, + and /, left out) So, A1 would be AAA, A2 would be AAB … continuing to AAZ, AAa, …, AA8, AA9, ABA, ABB etc.

Note that AAA would be numerically the same as A (both equal 0), but I need to keep the same number of digits across the whole sequence.

Doing this with only numbers is simple: =A1+1 and copy it all the way down the column.

Is there a simple and efficient way to do this either in sheets (maybe use an array formula?) or using a function? I will need to go at least to 40000 iterations (40000=KZK, if I did the calculation correctly).

Eventually, it will look something like this:

article_AAA

article_AAB

article_AAC

…

article_KZI

article_KZJ

article_KZK

I'm not much of a mathematician, but I suspect this site could help:
https://www.dcode.fr/base-n-convert

Best Answer

=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE((CHAR({{
 TRANSPOSE(SPLIT(    TEXTJOIN(",",1,REPT(ROW(INDIRECT("A"&65&":A"&68))&",",3844)),","));
 TRANSPOSE(SPLIT(","&TEXTJOIN(",",1,REPT(ROW(INDIRECT("A"&69&":A"&72))&",",3844)),","));
 TRANSPOSE(SPLIT(","&TEXTJOIN(",",1,REPT(ROW(INDIRECT("A"&73&":A"&74))&",",3844)),","));
 TRANSPOSE(SPLIT(","&TEXTJOIN(",",1,REPT(75&",",1561)),","))},{
 TRANSPOSE(SPLIT(REPT(TEXTJOIN(",",1,REPT({ROW(INDIRECT("A"&65&":A"&90))&",";
        ROW(INDIRECT("A"&97&":A"&122))&",";ROW(INDIRECT("A"&48&":A"&57))&","},62)),2),","));
 TRANSPOSE(SPLIT(REPT(TEXTJOIN(",",1,REPT({ROW(INDIRECT("A"&65&":A"&90))&",";
        ROW(INDIRECT("A"&97&":A"&122))&",";ROW(INDIRECT("A"&48&":A"&57))&","},62)),2),","));
 TRANSPOSE(SPLIT(REPT(TEXTJOIN(",",1,REPT({ROW(INDIRECT("A"&65&":A"&90))&",";
        ROW(INDIRECT("A"&97&":A"&122))&",";ROW(INDIRECT("A"&48&":A"&57))&","},62)),2),","));
 TRANSPOSE(SPLIT(REPT(TEXTJOIN(",",1,REPT({ROW(INDIRECT("A"&65&":A"&90))&",";
        ROW(INDIRECT("A"&97&":A"&122))&",";ROW(INDIRECT("A"&48&":A"&57))&","},62)),2),","));
 TRANSPOSE(SPLIT(REPT(TEXTJOIN(",",1,REPT({ROW(INDIRECT("A"&65&":A"&90))&",";
        ROW(INDIRECT("A"&97&":A"&122))&",";ROW(INDIRECT("A"&48&":A"&57))&","},62)),2),","));
 TRANSPOSE(SPLIT(TEXTJOIN(",",1,REPT({ROW(INDIRECT("A"&65&":A"&89))&","},62)),","));
 TRANSPOSE(SPLIT(TEXTJOIN(",",1,REPT(90&",",11)),","))},{
 TRANSPOSE(SPLIT(TRANSPOSE(REPT(TEXTJOIN(",",1,{ROW(INDIRECT("A"&65&":A"&90));
                 ROW(INDIRECT("A"&97&":A"&122));ROW(INDIRECT("A"&48&":A"&57))})&",",150)),","));
 TRANSPOSE(SPLIT(TRANSPOSE(REPT(TEXTJOIN(",",1,{ROW(INDIRECT("A"&65&":A"&90));
                 ROW(INDIRECT("A"&97&":A"&122));ROW(INDIRECT("A"&48&":A"&57))})&",",150)),","));
 TRANSPOSE(SPLIT(TRANSPOSE(REPT(TEXTJOIN(",",1,{ROW(INDIRECT("A"&65&":A"&90));
                 ROW(INDIRECT("A"&97&":A"&122));ROW(INDIRECT("A"&48&":A"&57))})&",",150)),","));
 TRANSPOSE(SPLIT(TRANSPOSE(REPT(TEXTJOIN(",",1,{ROW(INDIRECT("A"&65&":A"&90));
                 ROW(INDIRECT("A"&97&":A"&122));ROW(INDIRECT("A"&48&":A"&57))})&",",150)),","));
 TRANSPOSE(SPLIT(TRANSPOSE(REPT(TEXTJOIN(",",1,{ROW(INDIRECT("A"&65&":A"&90));
                 ROW(INDIRECT("A"&97&":A"&122));ROW(INDIRECT("A"&48&":A"&57))})&",",45)),","));
 TRANSPOSE(SPLIT(TRANSPOSE(TEXTJOIN(",",1,ROW(INDIRECT("A"&65&":A"&75)))&","),","))}}))), , 
 40001)), " ", ""))

0

Related Topic