Excel generate random letter A to Z using RANDBETWEEN

This Excel tutorial explains how to generate random letter A to Z / a to z or a mix using RANDBETWEEN Function.

You may also want to read

Assign unique random number using Excel

Use VBA to generate non-duplicated random number

Excel generate random letter A to Z using RANDBETWEEN

In order to generate random letter, we have to make use of two Functions.

– RANDBETWEEN

– CHAR

In the below sections, I will briefly go going through these two Functions. For details, click on the link above.

Excel generate random number using RANDBETWEEN

Before Excel 2007, Excel uses RAND Function to generate a random number that is greater than zero and smaller than 1.

Since Excel 2007, a new Function RANDBETWEEN was added, it is used to generate a random integer between two desired integers.

Syntax of RANDBETWEEN

RANDBETWEEN(bottom,top)

bottom	The minimum integer of the random number, this number is inclusive in the random range
top	The maximum integer of the random number, this number is inclusive in the random range. #Num Error will return if bottom is larger than top.

If you cannot use this function, enable Analysis TooPak under Developer tab in menu as below.

Excel Char Function to convert ASCII value to character

Excel Char Function converts ASCII value to a character. ASCII (American Standard Code for Information Interchange) uses 8-bit code units, an old encoding system which stores mainly numbers, lowercase letters a to z, uppercase letters A to Z, basic punctuation symbols, control codes. Many old systems still use this encoding system. 8 bit means the computer memory uses “8” digits with 1 and 0 combination (binary) to represent a character, 8 bits memory is equal to 1 byte.

Below is the ASCII value to character conversion for letter. For full conversion table, click here.

ASCII	Char
65	A
66	B
67	C
68	D
69	E
70	F
71	G
72	H
73	I
74	J
75	K
76	L
77	M
78	N
79	O
80	P
81	Q
82	R
83	S
84	T
85	U
86	V
87	W
88	X
89	Y
90	Z
97	a
98	b
99	c
100	d
101	e
102	f
103	g
104	h
105	i
106	j
107	k
108	l
109	m
110	n
111	o
112	p
113	q
114	r
115	s
116	t
117	u
118	v
119	w
120	x
121	y
122	z

Excel generate random letter A to Z / a to Z

To generate random letter, first decide whether you need lower case only, upper case only, or a mix of both.

If you want to generate lower case only, use ASCII value 97 to 122.

=CHAR(RANDBETWEEN(97,122))

If you want to generate upper case only, use ASCII value 65 to 90.

=CHAR(RANDBETWEEN(65,90))

If you want to generate a mix of lower case and upper case, use the VBA custom Function in the following section.

Excel VBA Code – generate random letter A to Z / a to z / a to Z

Public Function wRandomLetter(Optional rndType = 1) As String
    Randomize
    Select Case rndType
    Case 1
        randVariable = Int((122 - 65 + 1) * Rnd + 65)
        Do While randVariable > 90 And randVariable < 97
            randVariable = Int((122 - 65 + 1) * Rnd + 65)
        Loop
        wRandomLetter = Chr(randVariable)
    Case 2
        wRandomLetter = Chr(Int((122 - 97 + 1) * Rnd + 97))
    Case 3
        wRandomLetter = Chr(Int((90 - 65 + 1) * Rnd + 65))
    End Select
End Function

Excel VBA Syntax – generate random letter A to Z / a to z / a to Z

wRandomLetter(randType)

randType

Optional. Choose whether you want to generate lower case, upper case or a mix of both. 1 or no input – a mix of both 2 – lower case 3 – upper case

Excel VBA Example – generate random letter A to Z / a to z / a to Z

Formula	Explanation
=wRandomLetter()	Generate a letter which can be lower case or upper case
=wRandomLetter(1)	Generate a letter which can be lower case or upper case
=wRandomLetter(2)	Generate a letter which is lower case
=wRandomLetter(3)	Generate a letter which is upper case

Outbound References

https://support.office.com/en-us/article/Rnd-Function-503CD2E4-3949-413F-980A-ED8FB35C1D80