Fill two-dimensional array with random letters

Question

I'd like to fill a two-dimensional array in Excel (let's say A1:J20) with random letters. How can I do this using programming (or even without programming, if possible)?

danielpiestrak · Accepted Answer · 2012-08-28 16:31:41Z

1

A formula that returns a random lowercase letter between a and z would be:

=CHAR(97+RANDBETWEEN(0,25))

How It Works

The =char() formula returns the letter for the number code.

Note that 97 is for lowercase a, and 65 is for uppercase A.

The RANDBETWEEN function returns a random number between 0 and 25. we'll start with the letter a, and then add this random number to it to get the final result.

answered Aug 28, 2012 at 16:31

danielpiestrak

5,4793 gold badges33 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nigel Heffernan · Accepted Answer · 2012-08-28 16:54:27Z

It's easy in formulas.

First, put this formula into a cell somewhere: =CODE("A") - You should see the number 65. Then, try =CODE("Z") - this will show 90.

You now have the numeric codes for letters A and Z

What you actually want to do is calculate random numbers between 65 and 90, and then translate these numbers back into characters.

So here's the formula, for any cell, which wil obtain a random whole number between 65 and 90: =TRUNC(65 + (RAND() * (91 - 65))) - note the integer arithmetic I'm using has a truncation, so I'm adding 1 to the upper boundary...

Finally, feed the results into a function, CHAR() that returns a character from its ACSII code:

=CHAR(TRUNC(65 + (RAND() * (91 - 65))))

That should work...

Collectives™ on Stack Overflow

Fill two-dimensional array with random letters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related