## Explanation

The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.

In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by the RANDARRAY function, a new function in Excel 365. RANDARRAY returns 6 random numbers to INDEX, which then retrieves 6 random values from the named range **chars.** The results from INDEX are then concatenated together with the TEXTJOIN function.

In the example shown, the formula in D5 is:

```
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
```

Working from the inside out, the RANDARRAY function 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}
```

Note the array returned will vary with each instance of the RANDARRAY function. Also, because RANDARRAY is a volatile function, it will recalculate with each worksheet change.

This array of random numbers is returned directly to the INDEX function as the *rows* argument:

```
INDEX(chars,{14;5;21;7;25;3})
```

Because we are asking INDEX for 6 rows, we get back 6 results in an array like this:

```
{"N","E","U","G","Y","C"}
```

This array is returned to the TEXTJOIN function as the *text1* argument:

```
=TEXTJOIN("",1,{"N","E","U","G","Y","C"}) // returns "NEUGYC"
```

TEXTJOIN is set to use an empty string as the delimiter and to ignore empty values. With this configuration, TEXTJOIN simply concatenates all values together and returns a 6-character text string like "NEUGYC".

### Count chars programmatically

Instead of hardcoding the size of **chars** directly into the RANDARRAY function, you can use the COUNTA function to count the elements in the array and return that count to RANDARRAY:

```
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
```

This assumes **chars** does not contain any empty cells.

### Generate chars programmatically

Since the letters A-Z have underlying numeric code values, it is possible to generate the array of characters used to assemble text strings programmatically, instead of using a range. This can be done with the CHAR function and the SEQUENCE function.

To generate an array with all uppercase letters A-Z, which map to ASCII 65-90:

```
=CHAR(SEQUENCE(26,1,65,1)) // returns {"A","B","C",...}
```

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

```
=CHAR(SEQUENCE(26,1,97,1)) // returns {"a","b","c",...}
```

This code can be dropped into the original formula to replace **chars** like this:

```
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
```

### Other characters

The characters in the named range **chars** can be anything you like. If you add more than 26 characters (or fewer) adjust the number 26 as appropriate, or use COUNTA as explained above.

### Without Excel 365

It is possible to generate random text strings without Excel 365, but the formula is more tedious and redundant. Since we don't have a good way to get 6 random numbers all at once, we use the RANDBETWEEN function to get one random value 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 formula uses the INDEX function to retrieve one random value at a time from the named range **chars**, and the 6 results are concatenated together into a single text string. Line breaks added for readability.

It is also possible to generate values A-Z directly with CHAR and RANDBETWEEN like this:

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

In this version, RANDBETWEEN is returning a value between 65 and 90 (inclusive) that corresponds to the ASCII value for the letters A-Z (uppercase). The CHAR function translates the numeric value to a letter. As above, all results are concatenated together in a single text string.