How to generate random numbers and text

Author: Oscar Cronquist Article last updated on February 17, 2023

Microsoft Excel has three useful functions for generating random numbers, the RAND, RANDBETWEEN, and the RANDARRAY functions.

The RAND function returns a random decimal number equal to or larger than 0 (zero) and smaller than 1. Example: 0.600842025092928

The RANDARRAY function calculates an array of random numbers. It allows you to choose integers or decimal values, how many rows and columns to fill and the number range.

This is a new function available for Excel 365 subscribers in the monthly channel, it automatically spills values across rows and columns without the need to enter it as an array formula. This is called spilled array behavior and is a new feature in Excel.

RANDBETWEEN function returns random integers in a range you specify. The function has two arguments: bottom and top. An integer is a whole number (contrary a fractional number) that can be positive, negative, or zero.

RANDBETWEEN(bottom, top)

Bottom is the lowest number in the range of numbers you want to return. Top is the highest number in the range.

Example, RANDBETWEEN(1,10) generates random numbers between 1 to 10.

Formula in cell range A2:A11:

=RANDBETWEEN(1,10)

As shown above, there are duplicates. If you don't want duplicates, read this post: How to create a list of random unique numbers

What's on this page

Random numbers with a decimal
Random negative numbers with two decimals
Random negative numbers with three decimals
Random names
Random list
Create random text strings
Get *.xlsx file

Generate numbers with a decimal

In this example, the RANDBETWEEN function generates random numbers between 2 and 4 with a decimal.

Formula in cell range A2:A11:

=RANDBETWEEN(20,40)/10

Since it actually can't generate numbers with decimals the function generates values between 20 to 40. Divide the output with 10 and the range becomes 2.0 to 4.0.

Generate negative numbers with two decimals

In this example, the RANDBETWEEN function generates random numbers between -1 and -0.9 with two decimals.

Formula in cell range A2:A11:

=RANDBETWEEN(-100,-90)/100

The function generates numbers between -100 and -90. The returned number is then divided by 100. The range becomes -1.00 to -0.90.

Generate negative numbers with three decimals

Here, the RANDBETWEEN function generates random negative numbers between -1010.000 and -1000.000

Formula in cell range A2:A11:

=RANDBETWEEN(-1010000,-1000000)/1000

The function generates numbers between -1010000 and -1000000. The returned number is then divided by 1000. The range becomes -1010.000 to -1000.000

Generate random names

The formula in cell B3 returns random names from cell range A3:A13.

Formula:

=INDEX($A$3:$A$13,RANDBETWEEN(1,11))

Explaining formula in cell B3

generate random names 1

I recommend that you use Excel's built-in tool for examining formulas, it is a great feature that allows you to see the calculations steps at a pace you are comfortable with.

Go to tab "Formulas" on the ribbon. Press with mouse on "Evaluate Formula" button to open the "Evaluate Formula" dialog box.

Press with left mouse button on "Evaluate" button to move the calculation to the next step, continue press with left mouse button oning the "Evaluate" button until all steps have been shown. Press with left mouse button on the "Close" to dismiss the dialog box.

Step 1 - Generate random value between 1 and 11

RANDBETWEEN(1,11) returns a number between 1 to 11

Step 2 - Return a value of the cell at the intersection of a particular row and column

The INDEX function allows you to return a value from a given range.

INDEX(array, row_num, [column_num])

INDEX($A$3:$A$13, random_value)

The given range is $A$3:$A$13 and there are 11 cells in that range. Therefore, we need to return a random value between 1 and 11.

RANDBETWEEN(1, 11) returns a number between 1 to 11. The INDEX function then returns a random value from cell range $A$3:$A$13 in cell B3.

You can use the formula in the following post to return unique distinct names: How to create a list of random unique numbers

Generate a random list

generate random list

The following steps show you how to randomly shuffle a list. The image above shows names in cell range A3:A13.

Select cell range B3:B13
Type =RAND()
Press and hold CTRL + Enter. This will enter the formula in all cells at once, no need to copy the formula and paste to cells below.
Select cell range A3:B13
Press with right mouse button on on the selected cell range.
Press with left mouse button on Sort.
Press with left mouse button on "Custom Sort..." and a dialog box appears.
Sort by: Column B
Order: Smallest to Largest
Press with left mouse button on OK button.

The image above shows the list sorted in a random order.

Create random text strings

The image above demonstrates a formula in cell range A2:A11 that returns four random characters concatenated into one text string. The characters are lower letters between a and z.

Formula:

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

Explaining formula in cell A2

Step 1 - Return a random number between 97 and 122

So why return a number between 97 and 122? All characters have a numeric code and the CODE function lets you convert a character to a numeric code.

For example, a is 97 and z is 122. By generating random numbers between 97 and 122 we can create characters from a to z.

RANDBETWEEN(97,122)

Step 2 - Return a character specified by a code number

The CHAR function converts a number to the corresponding character. This is determined by your computer's character set.

CHAR(RANDBETWEEN(97,122))

returns a random character from a to z.

Step 3 - Combine four characters

The ampersand operator & combines different functions to a text string. The are four instances of the formula above, the ampersand concatenates the output from each and returns a text string containing four lower letters.

CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))

Get the Excel file

RANDBETWEEN-function.xlsx

Functions in this article

RANDBETWEEN INDEX CHAR

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

10 Responses to “How to generate random numbers and text”

John says:

January 18, 2017 at 1:58 pm

How do you create a random number between -38 and +34 ?

My test sometimes produces numbers outside the low and high.

Reply
- Oscar says:
  
  January 18, 2017 at 6:23 pm
  
  John
  
  Does this produce numbers outside -38 and 34?
  =RANDBETWEEN(-38,34)
John says:

January 19, 2017 at 1:09 am

Oscar, Thank you for your response.

Yes, the RANDBETWEEN does work with whole numbers.

I misstated the problem. I meant to state: random number between -38.5% and 34.1%. It is the RAND()function that creates numbers outside the High and Low percentages.

Sorry for the confusion.

Reply
Oscar says:

January 19, 2017 at 7:56 am

John

I understand, try this:

=RANDBETWEEN(-385,341)/10

or this

=RANDBETWEEN(-340,340)/1000 and format the cells %.

Reply
John says:

January 19, 2017 at 9:21 pm

10-4...worked fine. I used the 1000 divisor in order to create a true percentage result which is then used as a multiplier.
It sure looks simple when you see the answer...guess I just had a V8 moment.

(I still don't know why my RAND() function did not work properly, but am not willing to spend any more time trying to figure it out.)

Thanks again for your help. Keep up the good work.

JB

Reply
- Oscar says:
  
  January 20, 2017 at 11:48 am
  
  John,
  
  thank you. I had to google both 10-4 and V8. :-)
Atha says:

February 20, 2017 at 7:15 am

How to create a random number and text
Example :
If select cell A1, then result below
KSPC00009000-KSPC00009999

If select cell B1, then result below
KSPR00009000-KSPR00009999

Reply
- Oscar says:
  
  February 20, 2017 at 3:23 pm
  
  Atha,
  
  Cell A1:
  ="KSPC0000"&RANDBETWEEN(9000,9999)
  
  Cell B1:
  ="KSPR0000"&RANDBETWEEN(9000,9999)
Atha says:

February 21, 2017 at 7:49 am

Mr Oscar, thanks for your help, its work for me.

Reply
Atha says:

February 27, 2017 at 9:18 am

Mr Oscar,
How to Generate a New Number & Text to case below (VBA) :
textbox1 : save the result in Cell A2
commandbutton1 : Generate new random ie KSPC00009000-KSPC00009999

Generate a new number and not duplicate with range A2:A100
If there's a duplicate, then generate a new number again, until there's a unique value, save it and proceed to the next until it is finished.

I've tried this code but not running in case number & text
```
Private Sub cmdGenerate_Click()
    Dim Low As Long
    Dim High As Long
    Dim r As Long

    Low = 9000
    High = 9999
getNum:
    r = "KSPC0000" & Int((High - Low + 1) * Rnd() + Low)
    If IsNumeric(Application.Match(r, Worksheets("Sheet1").Range("A:A"), False)) Then GoTo getNum
    Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)(2).Value = r
    Me.Textbox1.Value = r

End Sub
```
Thanks

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form