There's a number of things going on here. First, this formula does not
generate random character: it generates a random number between 5 and
the number of characters in cell A1, and uses that random number as the
starting point of the MID function to return the 5 characters that
start at the random number. As written, this formula will never return
any of the first 5 characters in A1- is that by design?
To address your numbered questions:As written you can't ensure characters will not repeat, because if the
string in cell A1 is "access" for example, the C and the S repeat.
The reason you get a string of characters less than 5 chars long is
because the random number is sometimes greater than the length of the
string in A1 - 5. That is to say if the string in cell A1 is
ABCDEFGHIJ (10 chars long), when the random number is 7, the formula
starts at column 7 and attempts to return 5 characters. However, since
there are only 10 characters to begin with, the formula would return
GHIJ because there is no 5th character to return.
If you need to return 5 random characters every time from anywhere
within the cell A1 string, please consider this formula:
=MID(A1,ROUNDUP(RAND()*(LEN(A1)-1),0),1)&MID(A1,ROUNDUP(RAND()*(LEN(A1)-1),0),1)&MID(A1,ROUNDUP(RAND()*(LEN(A1)-1),0),1)&MID(A1,ROUNDUP(RAND()*(LEN(A1)-1),0),1)&MID(A1,ROUNDUP(RAND()*(LEN(A1)-1),0),1)
This formula is 5 MID functions that derive a random number between 1
and the number of characters in the string. It does this by using the
RAND function and the length of the A1 string -1, then *rounding that
number up*. That's an important change from the original formula
because the RAND function generates a fractional number between 0 and
1, and it's possible to return a zero argument for the MID function.
The original formula avoided this problem by adding 5 to the result of
the RAND function. Did you need to avoid the first 5 characters for
some reason?
Please let us know your thoughts!
