Excel formula: Generate random text strings - SupportMyMoto

# Excel formula: Generate random text strings

WRITTEN BY: supportmymoto.com STAFF

The brand new dynamic array formulation in Excel 365 make it a lot simpler to resolve sure difficult issues with formulation.

On this instance, the purpose is to generate an inventory of random 6-character codes. The randomness is dealt with by the RANDARRAY perform, a brand new perform in Excel 365. RANDARRAY returns 6 random numbers to INDEX, which then retrieves 6 random values from the named vary chars. The outcomes from INDEX are then concatenated along with the TEXTJOIN perform.

Within the instance proven, the system in D5 is:

Working from the within out, the RANDARRAY perform is used to generate an array containing six random numbers between 1-26:

```RANDARRAY(6,1,1,26,TRUE) // return array like {14;5;21;7;25;3}

Word the array returned will fluctuate with every occasion of the RANDARRAY perform. Additionally, as a result of RANDARRAY is a risky perform, it’ll recalculate with every worksheet change.
This array of random numbers is returned on to the INDEX perform because the rows argument:
INDEX(chars,{14;5;21;7;25;3})

As a result of we’re asking INDEX for six rows, we get again 6 ends in an array like this:
{"N","E","U","G","Y","C"}

This array is returned to the TEXTJOIN perform because the text1 argument:
=TEXTJOIN("",1,{"N","E","U","G","Y","C"}) // returns "NEUGYC"

TEXTJOIN is ready to make use of an empty string because the delimiter and to disregard empty values. With this configuration, TEXJOIN merely concatenates all values collectively and returns a 6-character textual content string like “NEUGYC”.
Rely chars programmatically
As a substitute of hardcoding the dimensions of chars straight into the RANDARRAY perform, you should utilize the COUNTA perform to depend the weather within the array and return that depend to RANDARRAY:

This assumes chars doesn’t comprise any empty cells.
Generate chars programmatically
For the reason that letters A-Z have underlying numeric code values, it’s potential to generate the array of characters used to assemble textual content strings programmatically, as an alternative of utilizing a spread. This may be carried out with the CHAR perform and the SEQUENCE perform.
To generate an array with all uppercase letters A-Z, which map to ASCII 65-90:

To generate lowercase letters a-z, which correspond to ASCII 97-122:

This code could be dropped into the unique system to switch “chars” like this:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))

Different characters
The characters within the named vary chars could be something you want. In the event you add greater than 26 characters (or fewer) modify the quantity 26 as acceptable, or use COUNTA as defined above.
With out Excel 365
It’s potential to generate random textual content strings with out Excel 365, however the system is extra tedious and redundant. Since we do not have a great way to get 6 random numbers , we use the RANDBETWEEN perform to get one random worth at a time:
=INDEX(chars,RANDBETWEEN(1,26))&
INDEX(chars,RANDBETWEEN(1,26))&
INDEX(chars,RANDBETWEEN(1,26))&
INDEX(chars,RANDBETWEEN(1,26))&
INDEX(chars,RANDBETWEEN(1,26))&
INDEX(chars,RANDBETWEEN(1,26))

This system makes use of the INDEX perform to retrieve one random worth at a time from the named vary chars, and the 6 outcomes are concatenated collectively right into a single textual content string. Line breaks added for readability.
Additionally it is potential to generate values A-Z straight with CHAR and RANDBETWEEN like this:

On this model, RANDBETWEEN is returning a worth between 65 and 90 (inclusive) that corresponds to the ASCII worth for the letters A-Z (uppercase). The CHAR perform interprets the numeric worth to a letter. As above, all outcomes are concatenated collectively in a single textual content string.
```
NOTE : Please do not copy - https://supportmymoto.com