0

I am attempting to make Excel generate a 6-character password string, exactly like TeamViewer (3 letters, 3 numbers). Is there a function I might be unaware of?

I have tried =CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(0,9)),CHAR(RANDBETWEEN(97,122)))&CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(0,9)),CHAR(RANDBETWEEN(97,122)))&CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(0,9)),CHAR(RANDBETWEEN(97,122)))&CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(0,9)),CHAR(RANDBETWEEN(97,122)))&CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(0,9)),CHAR(RANDBETWEEN(97,122)))&CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(0,9)),CHAR(RANDBETWEEN(97,122))), and here's an example of one of the results: ckjfs.

Please see above for the Formula.

The expected result is something like: aaa111, or 1aaa11. I don't want the Formula to allow something like 11aaaa, aaaaaa, or 1234aa.

SonAmyFan362
  • 1
  • 1

2 Answers2

1

Here is an option for you to consider:

enter image description here

Formula in A2:

=RANDBETWEEN(1,6)

Formula in B2:

=CHAR(RANDBETWEEN(IF(OR(RANK.EQ(A2,$A$2:$A$7)+COUNTIF($A$2:A2,A2)-1={1,2,3}),48,97),IF(OR(RANK.EQ(A2,$A$2:$A$7)+COUNTIF($A$2:A2,A2)-1={1,2,3}),57,122)))

Drag down.....

Formula in D2:

Excel 2016 with CONCAT:

=CONCAT(C2:C7)

Lower versions without CONCAT:

=C2&C3&C4&C5&C6&C7
JvdV
  • 70,606
  • 8
  • 39
  • 70
1

I can offer this rather long array formula:

=ArrayFormula(TEXTJOIN("",TRUE,IF(MID(TEXT(DEC2BIN(INDEX({7,11,13,14,19,21,22,25,26,28,35,37,38,41,42,44,49,50,52,56},RANDBETWEEN(1,20))),"000000"),{1,2,3,4,5,6},1)="0",
CHAR(CHOOSE({1,2,3,4,5,6},RANDBETWEEN(48,57),RANDBETWEEN(48,57),RANDBETWEEN(48,57),RANDBETWEEN(48,57),RANDBETWEEN(48,57),RANDBETWEEN(48,57))),
CHAR(CHOOSE({1,2,3,4,5,6},RANDBETWEEN(97,122),RANDBETWEEN(97,122),RANDBETWEEN(97,122),RANDBETWEEN(97,122),RANDBETWEEN(97,122),RANDBETWEEN(97,122))))))

I had to test it in Google Sheets because I only have an old version of Excel without the array concatenation features - it should work in later versions of Excel if you remove the ArrayFormula wrapper and enter it with Ctrl-Shift-Enter.

The idea is that there are only 20 ways of selecting 3 items (letters) out of 6 (letters and numbers) so choose one of them in binary (e.g. 010101) and generate letters where there are 1's and numbers where there are 0's.

enter image description here

EDIT

Confirmed working through Excel 2019, confirmed through CtrlShiftEnter:

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • 1
    Confirmed working in Excel :), very good Tom. About Excel version, untill recently I also had Excel 2013, but saw online one-time installation office-pro versions which are perfectly legal of just around 10 euro's. Might be worth it. – JvdV Jul 11 '19 at 05:06
  • Thank you for the edit - will investigate sourcing new version of Excel ;-) – Tom Sharpe Jul 11 '19 at 11:56