Channel: MrExcel.com

Category: Howto & Style

Tags: excelexcel tips and tricksmrexcelrandom letters in excelgenerating all combinations of two letters in excelusing the substitute function in excelgenerating random letters in excelusing the base function in excelmicrosoft excelbill jelen

Description: Jon wants to generate 6-letter sequences using only the letters B and P. So, for example: BBBBBB, PPPPPP, BPBPPB, BBBPPP, and so on. There are 64 such combinations and Bill shows you one way to solve this use BASE and SUBSTITUTE. Formulas used for one word: =SUBSTITUTE(SUBSTITUTE(BASE(RANDBETWEEN(0,63),2,6),"0","B"),"1","P") The red box suggested a shorter formula of: =CONCAT(CHAR(66+14*RANDARRAY(6,1,0,1,TRUE))) Formula for all 64 words, in sequence =SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(64,1,0),2,6),"0","B"),"1","P") Formula for those 64 words sorted randomly =SORTBY(SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(64,1,0),2,6),"0","B"),"1","P"),RANDARRAY(64)) If you have the number of letters in A3, then generate all words in sequence =SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(2^$A$3,1,0),2,$A$3),"0","B"),"1","P") Sort those with: =SORTBY(SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(2^$A$3,1,0),2,$A$3),"0","B"),"1","P"),RANDARRAY(2^$A$3))