Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Awesome...this will do brilliantly!
    Big cheers!!!
    Regards,
    Rudi

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  8. #8
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Regards,
    Rudi

Posting Permissions

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