# Thread: Help to generate random letters. (Excel 2000)

1. ## Help to generate random letters. (Excel 2000)

Hi,
I'm playing around with an idea, and it requires the generation of random letters. I was thinking of a volitile UDF that can be entered into a cell and autofilled as far as needed. The result must return random letters in upper and lower case. IE (in cells): H, r, K, D, B, x, E, q, a, L, j, .......
Any ideas how to do this as I'm stumped??? TIA

2. ## Re: Help to generate random letters. (Excel 2000)

Function RandomLetter() As String
Dim n As Byte
Application.Volatile
n = 65 + Int(26 * Rnd)
If Rnd > 0.5 Then
n = n + 32
End If
RandomLetter = Chr(n)
End Function

3. ## Re: Help to generate random letters. (Excel 2000)

Awesome...this will do brilliantly!
Big cheers!!!

4. ## Re: Help to generate random letters. (Excel 2000)

I was too lazy to work out how to omit the characters that are in between z and A, so I came up with this simpleone:

=MID("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQR STUVWXYZ",INT(52*RAND()+1),1)

5. ## Re: Help to generate random letters. (Excel 2000)

Another formula
<code>=CHAR(TRUNC(RAND()*26+CHOOSE(RANDBETWEEN(1,2 ),65,97)))</code>

6. ## Re: Help to generate random letters. (Excel 2000)

<img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> - I applaude your creativity Jan Karel...it works well. The nice thing is that I can modify the formula to: =MID("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQR STUVWXYZ",INT(52*RAND()+1),3) or any number and get a string output too! I could also jumble up the "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ" part to really get random "multi-character" output!
Cheers

7. ## Re: Help to generate random letters. (Excel 2000)

Tony...this also looks interesting. Could you explain the formula in bite-size chunks please. I believe 65 and 97 is the start point of ASCII characters, but how do you calc the characters from there???
Run this by me pls!
Tx

8. ## Re: Help to generate random letters. (Excel 2000)

=CHAR(TRUNC(RAND()*26+CHOOSE(RANDBETWEEN(1,2),65,9 7)))

CHOOSE(RANDBETWEEN(1,2),65,97) - This is used to generate either 65 or 97, as you noted the ASCII characters A and a

TRUNC(RAND()*26+CHOOSE(RANDBETWEEN(1,2),65,97)) - this results in a random number in the range 65-90 or 97-122

Finally the CHAR function converts the number into the corresponding letter.

9. ## Re: Help to generate random letters. (Excel 2000)

That ties it together. Nice function <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•