Random Text Generator

[patsfan]

Can anyone assist me in creating a type of RANDOM generator for text entries?

I am interested in a function that would select a random name from a prepopulated list. This would be similar to using the RAND function but would choose texts from a predetermined range instead of numbers.

My thought would be some type of UDF where I could enter a "=RANDNAME" function and the cell would populate from a specific list.

I could also use this code to randomly enter other types of text data like "=RANDCITY", or "=RANDSTATE" from prepopulated lists.


I've found a couple suggestions online using VBA or the RANDBETWEEN function combined with INDEX or VLOOKUP but I am wondering if this can be done as a UDF (which I have no clue on how to write).

 

[Luke M]

You could certainly create a UDF, but I'm not sure there's a need to, as using native function would certainly be faster and more efficient. Let's say you have a named range MyWords. A simple formula would be:

=INDEX(MyWords,RANDBETWEEN(1,ROWS(MyWords)

If you had defined the MyWords range to be dynamic so that it grows/shrinks as you add/delete words, the random word generator will continue to function correctly. If you really want a UDF, you could do something like this:

[pre]

Function RandWords() As String
Application.Volatile 'Recalculate every time
Dim WordList As String
Dim N As Variant
Dim MyValue As Integer

WordList = "Chandoo|Luke M|Hui|Excel|Code" 'Items sepearted by common delimiter
N = Split(WordList, "|")
MyValue = Int((UBound(N) + 1) * Rnd) ' Generate random value
RandWords = N(MyValue)

End Function

[/pre]

 

[patsfan]

Thanks Luke,

I see your point with using a dynamic list instead of a UDF.

The formula works great and is simple to use.

 

[Luke M]

Glad I could help out, thanks for the feedback.