How to Generate Dynamic Random Text Strings with Excel Formulas

In this video you learn how to generate random strings using formulas and functions in Excel.

I started the following ChatGPT prompt:

create an excel formula to generate a single random character or digit

The AI told me that...

You can use the following formula to generate a single random character or digit in Excel:

=CHAR(RANDBETWEEN(48, 122))

After finishing my brain-muscle workout and recording the video I asked ChatGPT to explain my final formula. This is what AI told me...

Please explain this Excel formula:

=MAP(SEQUENCE(B3),LAMBDA(jt,TEXTJOIN("",TRUE,MAP(SEQUENCE(B4),LAMBDA(c,CHAR(RANDBETWEEN(48,122)))))))

This formula is not a standard Excel formula, but rather a formula in the dynamic array formula language used in Microsoft 365 (formerly known as Excel 365).

It appears to generate a two-dimensional array of random strings of characters, where the number of rows is specified in cell B3 and the length of each string is specified in cell B4. The formula uses two dynamic array functions, SEQUENCE and MAP, along with two lambda functions (anonymous functions) defined with the LAMBDA function.

Breaking it down, we can read the formula as follows:

=MAP(SEQUENCE(B3), LAMBDA(jt, TEXTJOIN("", TRUE, MAP(SEQUENCE(B4), LAMBDA(c, CHAR(RANDBETWEEN(48, 122)))))))

SEQUENCE(B3) generates a dynamic array with the number of rows specified in cell B3.

The MAP function applies a lambda function to each element of the array generated by SEQUENCE(B3).

The first lambda function defined with LAMBDA(jt, ...) takes a single input argument, which is each element of the array generated by SEQUENCE(B3). The lambda function does not use the input value directly, but it is necessary to define a lambda function with a parameter in order to use it in a MAP function.

The second lambda function is defined with LAMBDA(c, ...) and takes a single input argument, which is each element of an array generated by another SEQUENCE function.

The MAP function applies the second lambda function to each element of the array generated by SEQUENCE(B4), which generates an array with the length of each string specified in cell B4.

The second lambda function uses the CHAR and RANDBETWEEN functions to generate a random integer between 48 and 122, which corresponds to the ASCII character codes for alphanumeric characters and some special characters. The CHAR function then converts the random integer to the corresponding character.
The MAP function then returns an array of randomly generated characters with the length specified in cell B4.

The TEXTJOIN function concatenates the array of random characters into a single string with no delimiter.

The MAP function then returns an array of randomly generated strings, with the number of strings equal to the number of rows specified in cell B3.

Видео How to Generate Dynamic Random Text Strings with Excel Formulas канала Igor Ovchinnikov