Random texts are a great, they can be used as the filler texts where you want to show representational text.
Or we can generate random text to use as password generator that is secure and hard to crack by notorious users using formulas in excel.
You can use Excel formulas to make totally random sentences. This is a great way to generate test data or dummy data-sets.
There are two ways to make the random sentences. You can use dynamic arrays in Excel or regular functions.
Random sentences formula – with dynamic arrays
A random sentence is nothing but random string made of alphabets & punctuation. For example,
Khssfd kwy atec. .,izsufddk f. <-Random sentence
The formula to generate random strings
=SUBSTITUTE(SUBSTITUTE(CONCAT( CHAR( RANDARRAY(200,1,97,124,TRUE) )),”{“,”.”),”|”,” “)
How does this formula works?
RANDARRAY(200,1, 97,124, TRUE): This dynamic array function generates a list of 200 random integers between 97 and 124.
what the function is RANDARRAY?
RANDARRAY is a part of newly introduced dynamic array functions in latest versions Excel. This function, just like other DA functions (FILTER, UNIQUE, SORT etc.) can generate an array of items and output them out on the spreadsheet.
What is 97 and 124?
97 and 124 are the ascii characters that are read by the computer where 97 stands for the alphabet a and 122 stands for alphabet z and two extra numbera are required for space and period.
CHAR(RANDARRAY(..)): This will turn the random numbers in to equivalent letters (a to z { and |)
CONCAT(CHAR(…)): This concatenates all the letters to one giant 120 letter string.
SUBSTITUTE(CONCAT(…), “{“, ” “): This replaces all { with SPACE.
SUBSTITUTE(…, “|”, “. “): This replaces all | with PERIOD-SPACE.