Formula to randomly extract a specified number of cells from a range of cells.(Microsoft Excel)


Japanese version.

Introducing a formula to generate random text from prepared characters.

It is mainly used to generate passwords.



The CONCAT, MAP, RANDARRAY, COUNTA, LAMBDA, and OFFSET functions are used.

Please change the red, blue, and green letters.

=CONCAT(MAP(RANDARRAY(number,1,1,COUNTA(candidate),TRUE),LAMBDA(v,OFFSET(Left end of candidate,0,v-1))))

Specify the number of characters in the generated result and the characters you wish to use side by side in the text range.
Since 10 is specified for the number of characters, 10 characters will be randomly extracted from candidate.

For a vertical text range, the formula is as follows.

=CONCAT(MAP(RANDARRAY(number,1,1,COUNTA(candidate),TRUE),LAMBDA(v,OFFSET(Left end of candidate,v-1,0))))