1

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

1

=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

  • 1
    That works! I was hoping there was a simpler answer, but I do like that it does not require the use of any additional cells on the spreadsheet, and that the formula only has to be entered into one cell. – Joe Mar 11 at 11:45
  • well, a simpler answer would require giving up on a-z,0-9 then it would be: =ARRAYFORMULA(SUBSTITUTE(ADDRESS(1,ROW(INDIRECT("A"&703&":A"&8123)),4),1,"")) – user0 Mar 11 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.