Random Alphabet generation

[thinksriniii]

Hi Guys,

Could someone help whether it is possible to generate random alphabets like we do for random number using Rand() function.


Cheers,
Dave Costa

 

[Jon von der Heyden]

Something like:

=CHAR(RANDBETWEEN(65,90))

 

[Richard Schollar]

Jon's formula does require the ATP to be installed in versions of Excel prior to 2007. The following is the same function but without the ATP dependency:

=CHAR(RAND()*26+65)

 

[thinksriniii]

Thanks a lot Guys for your help.

Cheers,
Costa

 

[Powercat]

The question I have in reference to the below random alphabetical generator formulas in excel:


=CHAR(RAND()*26+65)
Does not require ATP to be installed


=CHAR(RANDBETWEEN(65,90))
ATP to be installed in versions of Excel prior to 2007


Is there a way to make this to where the letters don't repeat themselves? Actually what I am looking for is to generate 26 characters (like that if the alphabet) in the same column but all random and no duplicate characters. Or if there is anyway to do this can you provide some aid?

 

[T. Valko]

The question I have in reference to the below random alphabetical generator formulas in excel:


=CHAR(RAND()*26+65)
Does not require ATP to be installed


=CHAR(RANDBETWEEN(65,90))
ATP to be installed in versions of Excel prior to 2007


Is there a way to make this to where the letters don't repeat themselves? Actually what I am looking for is to generate 26 characters (like that if the alphabet) in the same column but all random and no duplicate characters. Or if there is anyway to do this can you provide some aid?

Try this...

There must be a "header cell" in the range and this header cell must not contain one of the letters. It can be an empty cell.

So, let's use cell A1 as the header cell and it'll be an empty cell.

Create this named formula:

• Name: Nums
• Refers to: =ROW(INDIRECT("1:26"))

Enter this array formula** in A2 and copy down to A27:

=CHAR(64+SMALL(IF(ISNA(MATCH(CHAR(64+Nums),A$1:A1,0)),Nums),INT(RAND()*(26-(ROWS(A$2:A2)-1)))+1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Note that the letters will change every time a calculation takes place.

 

[shg]

Should it not be,

=CHAR(64 + SMALL(IF(ISNA(MATCH(CHAR(64+Nums), A$1:A1,0)), Nums), INT(RAND() * (25-(ROWS(A$2:A2)-1)))+1))

 

[T. Valko]

Should it not be,

=CHAR(64 + SMALL(IF(ISNA(MATCH(CHAR(64+Nums), A$1:A1,0)), Nums), INT(RAND() * (25-(ROWS(A$2:A2)-1)))+1))

I get the correct result(s) using either. Since I used 26 that's what it should be!

 

[T. Valko]

I get the correct result(s) using either. Since I used 26 that's what it should be!

Hope you're not offended by my style of humor!

To be more specific...

The 1st iteration of the formula should produce a random number from 1 to 26 to give a completely random 1st letter.

This portion of the formula does that:

INT(RAND()*(26-(ROWS(A$2:A2)-1)))+1)

For the 1st cell (A2) we can reduce that to:

INT(RAND()*26)+1

That will return a random number from 1 to 26.

If we use 25 then that 1st iteration in cell A2 becomes:

INT(RAND()*25)+1

Which will only generate random numbers from 1 to 25.

While this really doesn't effect the overall range of results for all letters it does reduce the randomness of the 1st iteration. In other words, if use 25 then we will never get the 26th smallest random result for cell A2.

 

[shg]

So, let's use cell A1 as the header cell and it'll be an empty cell.

I missed, that, sorry.