How to generate random letters in Excel

3 years ago 250

It's casual to instrumentality random values successful Microsoft Excel, but returning random letters mightiness permission you scratching your head. Fortunately, you lone request 2 functions.

Excel provides 3 functions for generating random values: RAND(), RANDARRAY(), and RANDBETWEEN(), but there's nary comparable relation for generating random letters. The information is, astir of won't request to make random numbers, but if you do, you privation to bash truthful quickly, right? Fortunately, by combining a mates of functions, you tin make random letters. In this article, I'll reappraisal those 2 functions and past amusement you however to harvester them to get speedy results.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system, but you tin usage earlier versions done Excel 2007. For your convenience, you tin download the objection .xlsx and .xls files. Excel Online supports these functions.

About RANDBETWEEN() and CHAR() successful Excel

You're astir apt acquainted with some of these functions already, but it mightiness ne'er hap to you to usage them together. Fortunately, they play nicely together.

First, let's reappraisal RANDBETWEEN(). This relation returns a random integer worth betwixt 2 specified values, each clip the expanse is calculated. That past portion is important, and we'll get backmost to that later. Its syntax,

RANDBETWEEN(bottom, top)

has 2 required arguments: Bottom specifies the tiny integer that tin beryllium returned, and apical specifies the largest integer that tin beryllium returned.

CHAR() returns a quality specified by a value, truthful it's bully for translating, which is however we'll usage it. Its syntax,

CHAR(value)

has lone 1 argument, which successful this case, volition beryllium a worth betwixt 1 and 255. Those values correspond characters successful the system's quality set, which was primitively based connected ANSI and for our purposes successful Excel is ASCII decimal. It's doubtful you volition request that information, but it doesn't wounded to cognize astir it.

Figure A shows a expanse of characters returned by the CHAR() relation utilizing the values 65 done 90 and 97 done 122. We're not acrophobic with the characters returned by immoderate different value.

Figure A

Can you spot wherever we're headed?

How to harvester the two

At this point, we person a relation that returns random numbers betwixt 2 specified values and a relation that converts values to alphabetic characters. By combining them, we tin make a relation that returns random letters.

To instrumentality a database of random upper-case and lower-case letters, participate the pursuing functions and transcript astatine will:

B2: =CHAR(RANDBETWEEN(65,90))

C2: =CHAR(RANDBETWEEN(97,122))

Figure B shows my lists. Your lists won't lucifer excavation due to the fact that the results are random.

Figure B

You tin alteration the bottommost and apical values to trim the fig of letters returned. For example, if you wanted to instrumentality random letters betwixt e and j, you'd usage the expression:

=CHAR(RANDBETWEEN(101, 106)

First, the RANDBETWEEN() relation returns a random missive betwixt bottommost and top. Then, the CHAR() relation translates that worth into a character, successful this case, letters of the English alphabet.

Returning a azygous random missive oregon a bid of azygous random letters is reasonably casual erstwhile you cognize however to harvester CHAR() and RANDBETWEEN(). How would you instrumentality a random acceptable of much than 1 letter?

How to concatenate results successful Excel

If you privation a bid of random strings, arsenic shown successful Figure C, simply concatenate 2 functions. Let's accidental you privation the archetypal missive to beryllium uppercase and the 2nd to beryllium lowercase. In this case, you'd usage the pursuing function:

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))

Figure C

The archetypal combined relation returns a random uppercase missive and the 2nd returns a random lowercase letter. The & quality is simply a concatenation operator. It simply combines the results of some functions; it doesn't full values.

You could harvester respective combinations for each kinds of strings.

How to person to literal values successful Excel

Earlier, I mentioned that RANDBETWEEN() calculates each clip you cipher that workbook. That means each clip you participate thing oregon property F9, these functions instrumentality antithetic characters. That mightiness not matter, but if it does, you'll privation to person the results into literal values. To rapidly person functions to their literal values, bash the following:

Select the values.
Press Ctrl + C to transcript them to the Clipboard.
In the Clipboard radical (on the Home tab), click the Paste dropdown and take Values (V) successful the Paste Values section. Doing truthful volition regenerate the functions with their results.

Stay tuned

This elemental operation yields flexible results—you tin randomly instrumentality arsenic galore letters arsenic you need. In a aboriginal article, we'll usage RANDBETWEEN() to make random content.