How can I generate random characters in Excel

T

Tayo

How can I generate random characters (both letters and numbers) into
different cells (both rows & columns) at he same time in excel?
 
A

Andrew Taylor

Put all the possible characters in a cell, and name it as "list".
Then
=MID(list,1+INT(RAND()*LEN(list)),1)
will give a random character from the list. Use as many of these
as you need.

Andrew
 
D

Daniel CHEN

Try the following formula:

=CHAR(RANDBETWEEN(32,126))

which generates a random char whose ascii code is between 32 and 126.
Please note that 32 corresponding to space.


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download

=================================
 
T

Tayo

Is the word 'list' in the formular the name of the header for the list? The
formular is not working. It's returning a #NAME? error. FYI, I am using
Excel 2003.
 
A

Andrew Taylor

"list" is an Excel Name for the cell containing the list of characters.

You could use a cell reference instead, e.g.:

=MID(A1,1+INT(RAND()*LEN(A1)),1)

where A1 contains (say)
1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
 
T

Tayo

Thanks Andrew but I have some other questions:

(1) How can I make sure that the characters generated are not repeated in
any of the cells?

(2) Some cells returns null characters while some others return characters
whose length is less than the specified length in LEN. How can I control the
length of characters the code will return?

Thanks
 
Top