Re: How can I control the length of characters a code will return?

Tech-Archive recommends: Speed Up your PC by fixing your registry

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:
(1) How can I make sure that the characters generated are not repeated
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.

(2) Some cells returns null characters...control the length of characters the code will return?
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!

.

Relevant Pages

  • Re: Data Validation
    ... Function NoHTML(str As String) As String ... Into a test cell and type this in B1: ... I am putting a string of text in a cell some of which may need to be HTML ... coded for formatting and which must not exceed 30 characters, ...
    (microsoft.public.excel.misc)
  • RE: Generating Passwords in Cells
    ... Special characters 1-0 are okay. ... Dim Cell As Range ... Private Function MakeNewPassword(UserName As String) As String ...
    (microsoft.public.excel.programming)
  • RE: Generating Passwords in Cells
    ... Special characters 1-0 are okay. ... Dim Cell As Range ... Private Function MakeNewPassword(UserName As String) As String ...
    (microsoft.public.excel.programming)
  • Re: Help creating a random string in Perl
    ... can pick one of the 7 characters in my source string) by using "print ... third and fourth characters to and check that the random number hasn't ... shuffling the array ... The idea here is to swap the current cell with some cell ...
    (perl.beginners)
  • Re: word table clear formatting
    ... > cell value cannot be overwritten with table.cell.range.text = ... it is placing the string in front of any characters already ... How did those characters get into the ... >> possible to corrupt the Excel sheet. ...
    (microsoft.public.office.developer.automation)