Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays.

1. Generate Array with ROW or COLUMN Functions

Array formulas often need to use “natural number” as parameters of the function, such as the second parameter of the LARGE function, OFFSET function in addition to the first parameter. Through the manual way to enter a constant array will be more trouble, and easy to make mistakes. Then we can use the ROW or COLUMN function in EXCEL to generate a sequence, this method is very convenient and fast.

The following formula produces a vertical array of natural numbers from 1 to 10.

{=ROW (1:10)}
Excel Array Construction1

The following formula produces a horizontal array of natural numbers from 1 to 10.

{=COLUMN(A:J)}
Excel Array Construction1

2. Generating two-dimensional Array from one-dimensional Array

Below we will show you how to construct a new two-dimensional array with two columns of data.

a. One-dimensional range rearrangement to generate two-dimensional array

If there is a list of students and the name column of that list contains 10 students’ names, we need to randomly place the students’ names in the name column into the cell range of 5 rows and 2 columns (a new two-dimensional array).

Excel Array Construction1

We can use the following steps to randomize the names of students in column B to a 5 row 2 column range, such as the cell range E1:F5.

STEP1# Select the cell range E1:F5

Excel Array Construction

STEP2# Enter the following array formula in the formula bar

=INDEX(B2:B11,RIGHT(SMALL(RANDBETWEEN(A2:A11^0,999)/1%+A2:A11,ROW(1:5)*2-{1,0}),2))
Excel Array Construction

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

Excel Array Construction

STEP4# You will see that the students’ names have been randomly placed in a two-dimensional range of array.

Let’s see how this array formula works.

=RANDBETWEEN(A2:A11^0,999)

The RANDBETWEEN function is used to generate an array of 10 values, where the elements are random integers between 1 and 1000. Since the elements are randomly generated, the size of the array elements is randomly ordered. The array formula generates an array of random integers as follows.

={484;203;468;525;702;220;13;163;386;54}

=RANDBETWEEN(A2:A11^0,999)/1%+A2:A11

The random integer array generated above is multiplied by 100 and then added to the ordinal array of 1 to 10. This ensures that the last two digits of the array elements are ordinal numbers 1 to 10.

={484;203;468;525;702;220;13;163;386;54}/1%+A2:A11

The above array formula returns the following array:

{48401;20302;46803;52504;70205;22006;1307;16308;38609;5410}

=ROW(1:5)*2-{1,0}

The ROW function generates a vertical array {1;2;3;4;5), and then subtracts a constant array {1,0} to produce a two-dimensional array of 5 rows and 2 columns.

Excel Array Construction

=SMALL()

The result is taken as the second argument to the SMALL function, which sorts the array after multiplication and addition processing. Since the original size of the array is random, after sorting, the ordinal number corresponding to the last two digits of each element is randomly ordered.

=RIGHT()

The RIGHT function is used to extract the last two digits of each element, and the INDEX function is used to return the student’s name in the corresponding position in column B. In this way, the names in column B can be randomly populated into a two-dimensional array of 5 rows and 2 columns.

b. Combining two columns of data to create a two-dimensional array

We can use the VLOOKUP function to query from the right to the left, and we can use the array operation and IF function to swap two columns of data to generate a new two-dimensional array.

Here is an example of how the VLOOKUP function can be used to reverse the query by constructing a new array.

If you have a table of employee information, and you need to find the employee’s number by the employee’s name, you can use the VLOOKUP function in combination with the IF function to construct a two-dimensional array to find the corresponding employee number.

The employee information table is as follows:

Excel Array Construction

To find the employee number by name, the steps are as follows.

STEP1# Select the cell B3

STEP2# Enter the following formula in the formula bar and press Enter

=VLOOKUP(B2,IF({1,0},E2:E11,D2:D11),2,)
Excel Array Construction

STEP3# As you can see, Jerry’s employee number has been found.

Let’s see how the above formula works.

The core part of the formula is IF ({1,0},E2:E11,D2:D11), which uses a horizontal array {1,0} and two vertical arrays to perform operations to achieve the position of the column where the employee’s name and work number are swapped. Its returned memory arrays are:

{"Tom",1;"Jerry",2;"Jacey",3;"Wendy",4;"William",5;"Ocean",6;"scott",7;"charies",8;"ceila",9;"nicole",10}
Excel Array Construction

The VLOOKUP function then queries the employee’s name in the two-dimensional array generated by the IF function and returns the corresponding employee number.

3. Extracting Sub-arrays From Data

In daily work, it is often necessary to extract part of the data from a column and reprocess it. For example, If you want to find out the list of employees who meet the specified requirements in the employee table.

The following describes how to extract some data from a column to form a subarray.

Suppose you have an employee salary table and you want to find out the names of employees whose salary is greater than $2000. The salary table is as follows.

Excel Array Construction

You can refer to the following steps to obtain a list of employees who meet the requirements.

STEP1# First you need to select the cell range E2:E11

Excel Array Construction

STEP2# Enter the following formula in the formula bar

=T(OFFSET(B1,SMALL(IF(C2:C11>2000,A2:A11),ROW(INDIRECT("1:"&COUNTIF(C2:C11,">2000")))),))
Excel Array Construction

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

Excel Array Construction

Let’s see how the above formula works.

=IF(C2:C11>2000,A2:A11)

First use the IF function to determine whether the salary meets the conditions, if the salary is greater than $2000, then return the employee’s ID, otherwise return the logical value FALSE.

Excel Array Construction

=ROW(INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″)))

The COUNTIF function is used to calculate the number of scores greater than 100 and is combined with the ROW function and INDIRECT function to generate a sequence of natural numbers from 1 to n.

Excel Array Construction

=SMALL(IF(C2:C11>2000,A2:A11),ROW( INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″))))

Use the SMALL function to find the employee number whose salary is greater than $2000 and return the following memory array.

={1;3;5;10}
Excel Array Construction

=OFFSET(B1,{1;3;5;10},)

The OFFSET function extracts the employee’s name from the result returned by the SMALL function and returns the following array of employee names.

={"Tom";"Jacey";"William";"nicole"}
Excel Array Construction

=T(OFFSET(B1,{1;3;5;10},))

Finally, the T function is used to convert the multi-dimensional reference returned by the OFFSET function into a memory array.

Excel Array Construction

4. Extracting Sub-array from a two-dimensional Array

The cell range A1:C10 contains data of text and numeric type, see the figure below.

Excel Array Construction

If you want to extract all the text-based data from the specified cell range A1:C10, then you can use the following array formula.

=T(INDIRECT(TEXT(SMALL(IF(A1:C10>="",ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT("1:"&COUNTIF(A1:C10,"*")))), "r0c00"),))
Excel Array Construction

Let’s see how the above formula works.

=IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10))

The IF function is used to determine the type of data in the cell range. If the cell value is the text, then let the cell’s line number multiplied by 100, and then add the cell’s column number, and then return a numeric result; if the cell value is not text type, then return the logical value FALSE.

={FALSE,102,FALSE;FALSE,202,FALSE;FALSE,302,FALSE;FALSE,402,FALSE;FALSE,502,FALSE;FALSE,602,FALSE;FALSE,702,FALSE;FALSE,802,FALSE;FALSE,902,FALSE;FALSE,1002,FALSE}
Excel Array Construction

=ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”)))

The COUNTIF function is used to calculate the number of text values in the range of cells A1:C10, and combined with the ROW function and INDIRECT function to generate a series of natural numbers from 1 to n.

={1;2;3;4;5;6;7;8;9;10}
Excel Array Construction

=SMALL(IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”))))

The SMALL function is used to extract the position information of the cell where the text is located and return a memory array.

={102;202;302;402;502;602;702;802;902;1002}
Excel Array Construction

=INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),)

The TEXT function is used to convert the location information to R1C1 reference style, and then use the INDIRECT function to return to the cell reference.

Excel Array Construction

=T(INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),))

Finally, the multi-dimensional references returned by the INDIRECT function are converted to memory arrays using the T function.

Excel Array Construction

5. Fill the Merged Cells by Array Formula

In the merged cells, only the first cell has a value, while the rest of the cells are empty cells. When we work with the data, we may need to fill the empty cells in the merged cells with the corresponding values to meet the needs of the calculation.

The following is a product sales table, we need to fill the empty cells in the merged cells with the corresponding region name. The data table is as follows:

Excel Array Construction

You can fill the data into the merged cells by using the following array formula.

=LOOKUP(ROW(A2:A13),ROW(A2:A13)/(A2:A13>""),A2:A13)
Excel Array Construction

Let’s See How This Formula Works:

=ROW(A2:A13)/(A2:A13>””)

This formula assigns a non-empty cell in column A to the row number of that cell, and returns the error value #DIV/O! for empty cells, and finally returns a memory array.

{2;#DIV/0!;#DIV/0!;#DIV/0!;6;#DIV/0!;#DIV/0!;#DIV/0!;10;#DIV/0!;#DIV/0!;#DIV/0!}
Excel Array Construction

Finally, the LOOKUP function is used to perform a fuzzy search and return the corresponding region name.

6. Convert Two-dimensional Array to one-dimensional Array

Some functions only support one-dimensional array as their arguments, not two-dimensional array. For example, the second argument of the MATCH function, the second argument of the LOOKUP function, and so on. If you want to complete the query in a two-dimensional array, you need to first convert the two-dimensional array to a one-dimensional array.

In the figure below, the cell range A1:C4 is a two-dimensional array, by using the following formula you can return the maximum value less than or equal to 100 in the cell range; LOOKUP function will perform a fuzzy search from a one-dimensional array returned by the SMALL formula, and return the value that matches the conditions.

The formula is as follows.

=LOOKUP(100,SMALL(A1:C4,ROW(1:12)))
Excel Array Construction

Let’s see how this formula works:

=SMALL(A1:C4,ROW(1:12))

Because the cell range is 4 rows and 3 columns, it is a two-dimensional array containing 12 elements. You can generate a sequence of natural numbers from 1 to 12 by using the ROW function. Then use the SMALL function to sort the two-dimensional array and return a one-dimensional memory array. The result is as follows:

={16;60;78.6;79;97;97;99;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}
Excel Array Construction

The LOOKUP function performs a fuzzy lookup by row and ignores the error value #NUM!. Finally, the maximum value less than or equal to 100 is returned, which is 99.

How to Generate Random Passwords in Excel

This post will teach you how to quickly generate a random password with Formula in Excel. You can combine a series of worksheet functions to create a complex formula to generate a random password in Microsoft Excel. How do I create an random code generator in Excel.

1. Video: Generate Random Passwords or Letters in Excel

This tutorial video provides step-by-step instructions on how to use Excel’s built-in functions to generate random passwords or letters in Excel, making it easy to create secure and unique passwords for a variety of purposes.

2. Generate Random Password

If you want to generate a random and secure password in Excel, you can use a combination of the CHAR function, the RANDBETWEEN function and concatenation operator to build a new Excel formula.

Just use the following steps:

Step1: Select one cell that you want to generate the random password,

Step2: then enter this formula in that Cell

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(100,999)&CHAR(RANDBETWEEN(42,43))

Step3: press Enter key to apply this formula. You will see that it will generate an eight characters password.

generate random passwords1

3. Random Letter Generator in Excel

If you want to create a series of random letters or strings in Microsoft Excel Spreadsheet. and you can use built-in functions such as the CHAR function, which can convert a number to its corresponding ASCII character.

For example, to generate a 5-character string of uppercase letters, and you can use the following formula based on the CHAR function and the RANDBETWEEN function.

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))
How to Generate Random Passwords in Excel 1.png

This formula generates a random uppercase letter for each position in the string. By copying and pasting the formula into multiple cells, users can generate a series of unique random strings for various purposes, such as generating temporary passwords or unique IDs.

4. Generate Random Uppercase Letter

If you want to generate a random password only contain alpha characters (random uppercase letter string), you can use the following formula:

=CHAR(RANDBETWEEN(65,90))
generate random passwords2

5. Generate Random Lowercase Letter String

If you want only to generate a random lowercase letter string, you can use the below formula:

=CHAR(RANDBETWEEN(97,122))
generate random passwords3

6. Generate Random Password Only Contain Numeric Characters

If you want to generate a random password only contain 6-digit numeric characters, you can use the following formula:

=RANDBETWEEN(100000,900000)
generate random passwords4

7. Generate Random Symbol String

If you want to generate a random symbol string, just use the following formula:

=CHAR(RANDBETWEEN(33,47))
generate random passwords5

This formula will randomly generate one of the following specific characters: !”#$%&'()*+,-./

We talked that the CHAR function will generate lots of specific characters, and if you just want to create a password that can be type on the keyboard, so you should keep the specific character simple and avoid to use characters like ¶, Œ, or ©.

8. Conclusion

Generating random passwords in Excel can be a quick and convenient way to create secure and unique passwords for various purposes. By using a combination of built-in functions, users can generate passwords of different lengths and complexity levels, as well as customize them to include different character sets.

9. Related Functions

  • Excel CHAR function
    The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
  • Excel RANDBETWEEN Function
    The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

How to Generate Random Values by a List in Different Cases in Excel

Sometimes we may create some fake data for testing or some other purpose. If we want to create an amount of numbers, we may feel it is annoying by entering random numbers one by one. For this instance, we can use RANDBETWEEN function to create a lot of numbers between a certain range quickly. On the other side, sometimes we may want to create random values refer to a given list, for example select some random numbers from a given number list, you can also select objects by given list as well. This article will show you the way to generate random values, and also generate values from a given list.

Method 1: Generate Random Numbers by RANDBETWEEN Function


Step 1: Select a range for saving the random numbers. Select a blank cell as the first cell in your selected range. Then enter the formula =RANDBETWEEN(1,100). In this case, we will generate numbers between 1 and 100.

How to Generate Random Values in Excel1

Step 2: Press Enter. We get a random number 57. Just between our specified numbers.

How to Generate Random Values in Excel2

Step 3: Drag the fill handle to fill the selected range. Now we create a range of random data. You may notice that the first cell value is changed. That’s because the formula is applied to other cells so after releasing your mouse to complete the task, it was recalculated.

How to Generate Random Values in Excel3

Method 2: Generate Random Numbers by a Given List


Prepare a list with fruit.

How to Generate Random Values in Excel4

Step 1: Select a blank cell, enter the formula =INDEX($D$2:$D$11, RANDBETWEEN(2, 11)). In this formula $D$2:$D$11 is the list area, 2 is the first row number for the area, and 11 is the last row number for the area. You can change parameters per your demands.

How to Generate Random Values in Excel5

Step 2: Press Enter to check result. Verify that the random value is generated properly and it is also listed in original fruit list.

How to Generate Random Values in Excel6

Step 3: Drag the fill handle down till F6. Verify that fruits are generated by random.

How to Generate Random Values in Excel7

NOTICE:

  1. In this step, when dragging fill handle to fill other cells, some errors may occur, for example:
  2. How to Generate Random Values in Excel8

You just need to double click on error cell and re-apply the formula.

  1. Sometimes the random values may be duplicate. See above screenshot, ‘Pear’ are displayed twice.

Method 3: Generate Random Numbers by a Given List Between Certain Range


Prepare a list with numbers. If we want to generate random values from this list and at the same time between number 1 to 50, how can we do? Let’s see steps below.

How to Generate Random Values in Excel9

Step 1: Select a blank cell, enter the formula =INDEX($A$1:$A$14, RANDBETWEEN(1,50)). Then press Enter to check if the generated number matches the two criteria.

How to Generate Random Values in Excel10

Obviously, number 37 is between range 1 to 50, and it is also listed in original list.

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel RANDBETWEEN Function
    The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

How to Pick a Random Name from a List in Excel

This post will guide you how to pick a random name from a list in Excel. How do I select random value from a list or a table with a formula in Excel 2013/2016.

Pick Random Name from a List


Assuming that you have a list of names in range B1:B6, and you want to pick random names from this list. How to do it. You can use a formula based on the INDEX function, the RANDOMBETWEEN function and the COUNTA function to achieve the result. You can use one of the following formulas:

=INDEX($B$2:$B$6,RANDBETWEEN(1,COUNTA($B$2:$B$6)),1)

Or

=INDEX($B$2:$B$6,RANDBETWEEN(1,ROWS($B$2:$B$6)),1)

Type this formula into a blank cell and press Enter key on your keyboard. And then drag the Fill Handle down to list the random names that you need. And then press F9 key, it will generate the different name list randomly.

pick random names from list1 pick random names from list2

Note: you should know that the name list will be changed when you refresh the current worksheet.

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel COUNTA function
    The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…
  • Excel RANDBETWEEN Function
    The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

 

How to Generate Random Numbers between Two Numbers in Excel

This post will guide you how to generate random numbers between two numbers in Excel. How do I generate random numbers between two numbers without duplicates with VBA macro in Excel.

Generate Random Numbers between Two Numbers with Formula


If you want to generate random numbers between two given numbers, you can use a formula based on the RANDBETWEEEN function in Excel.

For example, you need to generate random numbers between 10 and 100 numbers, you can use the following formula:

=RANDBETWEEN(10,100)

Type this formula into a blank cell, and press Enter key on your keyboard, and then drag the AutoFill handle to other cells to generate more random numbers.

generate random numbers between two numbers1

If you want to generate random number between 0 and 1, you can directly use the RAND function. Like this:

=RAND()

generate random numbers between two numbers2

Generate Random Numbers between Two Numbers with VBA


You can also use VBA code to define a new function to achieve the same result of generating random numbers with 2 decimal places between two given numbers in Excel. Or you can generate random numbers with given decimal places in a given range of cells in Excel. Here are the steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

generate random numbers between two numbers3

Public Function RandomRealNumbers(lowNum As Long, highNum As Long, _
Optional decN As Integer)
   If IsMissing(decN) Or decN = 0 Then
      Randomize
      RandomNumbers = Int((highNum + 1 - lowNum) * Rnd + lowNum)
   Else
      Randomize
      RandomNumbers = Round((highNum - lowNum) * Rnd + lowNum, decN)
   End If
End Function

#5 back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=RandomRealNumbers(10,100,2)

#6 drag the AutoFill handle over other cells to  generate random numbers with two decimal places between two given numbers 10 and 100.

generate random numbers between two numbers4

Related Functions


  • Excel RANDBETWEEN Function
    The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

 

Randomly Select Cells

This post will guide you how to randomly select cells from a list or table in Excel. How to get random value form a range of cells in an Excel Spreadsheet.

Assuming that you have a list of data (B1:B7) in a worksheet, and you want to get 3 random cells or values from those data, how to achieve it. This post will introduce two method to get random value from a range of cells. Randomly Select Cells with Formula. Or how to use an Excel User Defined Function to select cells randomly.

Randomly Select Cells with a Formula


If you want to randomly select cells from a range of cells, you can use a formula based on the INDEX function, the RANDBETWEEN function and the Rows function. Like this:

=INDEX($B$1:$B:$7,RANDBETWEEN(1,ROWS($B$1:$B:$7)),1)

Type this formula into a blank cell C1, and press Enter key in your keyboard, and then drag the AutoFill Handle over other two cells to apply this formula to get 3 random cells.

randomly select cells1

Randomly Select Cells with a User Defined Function


You can also write an User Defined Function with VBA code to quickly select random cells from a range of cells in Excel. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

randomly select cells2

Function RandomSelectCells(R As Range)
 Dim i As Integer
 Randomize
 i = Int(R.Count * Rnd + 1)
 RandomSelectCells = R.Cells(i).Value
 End Function

#5 back to the current worksheet, try to enter the below formula in Cell C1.

=RandomSelectCells($B$1:$B$7)

randomly select cells3

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel RANDBETWEEN Function
    The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….
  • Excel ROWS function
    The Excel ROWS function returns the number of rows in a cell reference.The syntax of the ROWS function is as below:= ROWS(array)…

 

Generate Random Character Strings

This post will guide you how to generate a random character string in excel. How do I create a random passwords, text strings with a formula in excel. How do I generate random strings of characters with VBA code in excel. How to generate random alphanumeric values in excel.

Generate Random Character Strings with Formula


To generate random character strings in excel, you should be generate random numbers, letters, and special characters. We have talked that how to generate a random number string with Excel RANDBETWEEN function in excel.

Generate random Number

To generate random number string, you can use a formula based on the RANDBETWEEN function. like this:

=RANDBETWEEN(0,9)

generate random strings1

This function will generate a random number between 0 and 9.

Generate random Letter String

To generate random uppercase or lowercase letter string in excel, you can create a formula base on the CHAR function and the RANDBETWEEN function. like this:

=CHAR(RANDBETWEEN(65,90))

or

=CHAR(RANDBETWEEN(97,122))

The first formula will generate random uppercase letter between A and Z.

generate random strings2

The second formula will generate random lowercase letter between a and z.

generate random strings3

Generate Random Specific Character

To generate random specific character in excel, you can use the below formula:

=CHAR(RANDBETWEEN(33,47))

generate random strings4

This formula will generate random specific characters, such as: #$!/

Generate random 5-digit number

To generate a random 5-digit number,you can use the following formula:

=RANDBETWEEN(10000,99999)

generate random strings5

Generate Random Three Letters String

We have know how to create a random uppercase letter and lowercase letter, if you want to create a random 3-letters string, you can use the following formula:

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

generate random strings6

Generate Random Character String with VBA Code


You can also use an VBA Macro Code to generate a random character string in a selected cell in Excel. You just need to do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

generate random strings7

Sub createRandomString()
    Dim rString As String
    Dim iCount As Integer
    Randomize
    For iCount = 1 To 5
        If Int((2 * Rnd) + 1) = 1 Then
            rString = rString & Chr(Int(26 * Rnd + 65))
        Else
             rString = rString & Int(10 * Rnd)
        End If

    Next iCount
    ActiveSheet.Range("B2").Formula = rString
End Sub

 

#5 back to the current worksheet, then run the above excel macro. Click Run button.

generate random strings8

#6 Let’s see the result.

generate random strings9

Related Functions


  • Excel CHAR function
    The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
  • Excel RANDBETWEEN Function
    The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

Related Posts


  • Generate Random Passwords
    To generate a Random and secure password in Excel, you can use a combination of the CHAR function, the RANDBETWEEN function and concatenation operator to build a new Excel formula….

Excel RANDBETWEEN Function

This post will guide you how to use Excel RANDBETWEEN function with syntax and examples in Microsoft excel.

Description

The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify. Each time when the worksheet is calculated or opened, a new random integer number is generated. So you can use the RANDBETWEEN function to generate a random integer number between tow given values.

The RANDBETWEEN function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.

The RANDBETWEEN function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the RANDBETWEEN function is as below:

= RANDBETWEEN (bottom,top)

Where the RANDBETWEEN function arguments are:

  • bottom -This is a required argument. The smallest integer number that RANDBETWEEN Function will return.
  • top – This is a required argument. The largest integer number that RANDBETWEEN Function will return.

 Note:

  • If the bottom argument is greater than the given top value, the RANDBETWEEN function will return #NUM! Error.
  • If either of the given arguments are non-numeric, this function will return #VALUE! Error.

Excel RANDBETWEEN Function Examples

The below examples will show you how to use Excel RANDBETWEEN Function to generate a random integer between two supplied integer numbers.

1# get a random number between 1 and 200, enter the following formula in Cell B1.

=RANDBETWEEN(1,200)

excel randbetween examples1

2# get a random number between -10 and 10, enter the following formula in Cell B2.

=RANDBETWEEN(-10,10)

excel randbetween examples2

Related Functions


  • Excel RAND function
    The Excel RAND function returns a random real number that is greater than or equal to 0 and less than 1. The syntax of the RAND function is as below:=RAND ()…

More Excel Randbetween Function Examples


  • Generate Random Character Strings
    To generate random character strings in excel, you should be generate random numbers, letters, and special characters. We have talked that how to generate a random number string with Excel RANDBETWEEN function in excel…
  • Generate Random Passwords
    To generate a Random and secure password in Excel, you can use a combination of the CHAR function, the RANDBETWEEN function and concatenation operator to build a new Excel formula….
  • Randomly Select Cells
    If you want to randomly select cells from a range of cells, you can use a formula based on the INDEX function, the RANDBETWEEN function and the Rows function…..