Tuesday, 31 December 2013

How to generate random character strings in a range in Excel

How to generate Alpha-Numeric strings in a range in Excel
In Microsoft Excel , Sometimes you may need to generate random strings , such as different passwords , uniqued ids with Numeric or Alpha Numeric combinaiton.It can be done as :

1. To create a random 5-digit number between 10000 and 99999, use this formula: 

=RANDBETWEEN(10000,99999)


Now press Enter key, a 5-digit number will be display in a cell, then select the cell and drag the fill handle across to the range that you want to contain this formula, a range of 5-digit numbers have been generated, see screenshots:



2. To create a random letter, use this formula: 

=CHAR(RANDBETWEEN(65,90))

This formula can generate a random letter from a to z.



3. To create a Alpha-Numeric String: 
If you need to create Alphanumeric Strings with special characters, you need to use & character to join letters and the letters. 

See the following example how it is generationg Alpha Numeric string with speical character '-'
The following formula generates a Alpha Numeric String( 3 Alpha &"-"& 3Numeric&3 Alpha) of length 11.

=CHAR(RANDBETWEEN(65,90))&
CHAR(RANDBETWEEN(65,90))&
CHAR(RANDBETWEEN(65,90))&"-"&
RANDBETWEEN(1001,5000)&
CHAR(RANDBETWEEN(65,90))&
CHAR(RANDBETWEEN(65,90))&
CHAR(RANDBETWEEN(65,90))

The result would be as follows :





Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts