How to Generate a Random String from a List in Excel (5 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will learn 5 different ways to generate a random string from a list in Excel with ease.


How to Generate Random String from a List in Excel: 5 Ways

1. Use INDEX & RANDBETWEEN Functions to Generate a Random String from a List

I will show you how to use a formula using the INDEX and RANDBETWEEN functions to generate a random string from a list.

❶ First, insert the following formula in cell D5.

=INDEX($B$5:$B$12,RANDBETWEEN(5,ROWS($B$5:$B$12)))

❷ Then press the ENTER button.

Use INDEX & RANDBETWEEN Functions to Generate a Random String from a List

❸ After that drag the Fill Handle icon from cell D5 to D7.

So, you will get some random strings generated out of a list.

Formula Breakdown

➤ ROWS($B$5:$B$12)

Here, the ROWS function counts the row numbers from $B$5 to $B$12.

➤ RANDBETWEEN(5,ROWS($B$5:$B$12))

The RANDBETWEEN function creates some random number between 5 and the value returned from ROWS($B$5:$B$12).

➤ INDEX($B$5:$B$12,RANDBETWEEN(5,ROWS($B$5:$B$12)))

The INDEX function generates some random strings from the range $B$5:$B$12.


2. Extract a Random String from a List in Excel Using INDEX & RANK.EQ Functions

Here, I will show you how to write a formula with the INDEX and RANK.EQ functions to extract a random string from a list in Excel.

❶ First of all, insert the following formula in cell E5.

=INDEX($B$5:$B$12,RANK.EQ(C5,$C$5:$C$12),1)

❷ Then hit the ENTER button.

Extract a Random String from a List in Excel Using INDEX & RANK.EQ Functions

❸ Drag the Fill Handle icon from cell E5 to E7.

Now you will see 3 random strings extracted from the List column.

Formula Breakdown

➤ RANK.EQ(C5,$C$5:$C$12)

The RANK.EQ function generates a ranking of numbers from the range $C$5:$C$12.

➤ INDEX($B$5:$B$12,RANK.EQ(C5,$C$5:$C$12),1)

The INDEX function generates some random strings from the range $B$5:$B$12.


3. Combine CHOOSE & RANDBETWEEN Functions to Produce Random Strings from a List

In this section, I will use the CHOOSE & RANDBETWEEN functions to produce some random strings from a list.

❶ At first, insert the following formula in cell D5.

=CHOOSE(RANDBETWEEN(1,6),$B$5,$B$6,$B$7,$B$8,$B$9,$B$10)

❷ Then hit the ENTER button.

Join CHOOSE & RANDBETWEEN Functions to Produce Random Strings from a List

❸ Now drag the Fill Handle icon from cell D5 to D7.

After that, you will see three random strings have been pulled out of the Product List column.

Output: Join CHOOSE & RANDBETWEEN Functions to Produce Random Strings from a List

Formula Breakdown

➤ RANDBETWEEN(1,6)

Here, the RANDBETWEEN function generates a random number between 1 to 6.

➤ CHOOSE(RANDBETWEEN(1,6),$B$5,$B$6,$B$7,$B$8,$B$9,$B$10)

The CHOOSE function chooses a cell in the range B5 to B10 based on the value returned by RANDBETWEEN(1,6).


4. Generate Random Strings  from a List with TEXTJOIN, INDEX, & RANDARRAY Functions in Excel

In this method, I will make a formula with the TEXTJOIN, INDEX, and RANDARRAY functions to generate random strings from a list.

❶ First, insert the following formula in cell D5.

=TEXTJOIN("",1,INDEX($B$5:$B$12,RANDARRAY(4,1,1,7,TRUE)))

❷ Then press the ENTER button.

Generate Random String  from a List with TEXTJOIN, INDEX, & RANDARRAY Functions in Excel

❸ Drag the Fill Handle icon from cell D5 to D7.

Finally, you will see some random strings generated from the List column.

Formula Breakdown

➤ RANDARRAY(4,1,1,7,TRUE)

Here, the RANDARRAY function generates 4 random numbers between 1 to 7.

➤ INDEX($B$5:$B$12,RANDARRAY(4,1,1,7,TRUE))

The INDEX function returns data from the range $B$5:$B$12 based on the number returned from  RANDARRAY(4,1,1,7,TRUE).

➤ TEXTJOIN(“”,1,INDEX($B$5:$B$12,RANDARRAY(4,1,1,7,TRUE)))

The TEXTJOIN function merges the 4 letters returned by INDEX($B$5:$B$12,RANDARRAY(4,1,1,7,TRUE)).


5. Use VLOOKUP & RANDBETWEEN Functions to Produce Random Strings from a List in Excel

Here, I will create a formula using the VLOOKUP & RANDBETWEEN functions to produce some random strings from a list in Excel.

To demonstrate, I have used an Excel Table here named “Tbl”.

❶ At first, insert the following formula in cell E5.

=VLOOKUP(RANDBETWEEN(MIN(Tbl[No.]),MAX(Tbl[No.])),Tbl,2,0)

❷ Then press the ENTER button.

Use VLOOKUP & RANDBETWEEN Functions to Produce a Random String from a List in Excel

❸ Drag the Fill Handle icon from cell E5 to E7.

After that, you will see the random strings generated from the Product column.

Output: Use VLOOKUP & RANDBETWEEN Functions to Produce a Random String from a List in Excel

Formula Breakdown

➤ RANDBETWEEN(MIN(Tbl[No.]),MAX(Tbl[No.]))

Here, the RANDBETWEEN function returns a random number between the minimum and maximum value returned by MIN(Tbl[No.]) and MAX(Tbl[No.]) respectively.

➤ VLOOKUP(RANDBETWEEN(MIN(Tbl[No.]),MAX(Tbl[No.])),Tbl,2,0)

The VLOOKUP function extracts a string based on the value returned by RANDBETWEEN(MIN(Tbl[No.]),MAX(Tbl[No.])). The extraction is done from the 2nd column of the table named Tbl.


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


Conclusion

To sum up, we have discussed 5 ways to generate a random string from a list in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP. And please visit our website Exceldemy to explore more.


<< Go Back to Random Number in Excel | Randomize in Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo