Generating dummy text in Excel table

When I want to create a lorem ipsum-like text in MS Word I use

=rand()

and it works perfectly. I immediately get a passage of mumbo-jumbo text which I can use for my aims.

I wasn't able to find the same for Excel, did I search bad? I found this blog which describes function

=pwrRANDOMTEXT()

however, it is not recognized on my Excel and throws formula error. I want to select table or range and fill it with random words, ideally with adjustable text length.

Is there a really working lorem ipsum-generator in Excel?

P.S. Excel version is latest, included in O365 Apps for Enterprise. Version 2104 Build 13929

2 Answers

Method 1 - Randomly chosen characters arranged into word-like and paragraph-like strings.

This formula could probably use some tweeking, but it can produce a string of characters that are "sentence-like".

=LET( letterPool, 800, sentDelimiter, ". ", cArray, RANDARRAY( 1, letterPool, 97, 122, TRUE ), wArray, RANDARRAY( 1, letterPool, 0, 1 ), wordStream, INDEX( CHAR( IF( wArray>0.8, 32, cArray ) ), 1, SEQUENCE( 1, letterPool ) ), sArray, RANDARRAY( 1, letterPool, 0, 1 ), sentences, INDEX( IF( sArray<0.02, sentDelimiter, wordStream ), 1, SEQUENCE( 1, letterPool ) ), pArray, RANDARRAY( 1, letterPool, 0, 1 ), paragraphs, INDEX( IF( pArray<0.01, "." & CHAR(10), sentences ), 1, SEQUENCE( 1, letterPool ) ), PROPER( SUBSTITUTE( TRIM( CONCAT( paragraphs, "." ) ), " .", "." ) ) )

Where it says 800, you can choose the length that you want. NB: it used Rand functions, so it is volatile and we recalc every time you enter data or press F9.

Method 2 - a set of randomly selected sentences randomly formed into paragraphs.

This method produces real sentences that you define and it randomly selects and places them into 3 paragraphs. Unlike the method above, you cannot control the absolute length of the string, but you can alter its relative size and shape. It is hard coded to produce 3 paragraphs, but you can follow the logic to add or delete that to your needs. Like the other approach - it is random, so it is volatile.

First, here is the basic formula with simple phrases to make it more readable.

=LET( p1Sent, 3, p2Sent, 5, p3Sent, 3, pghDelimiter, CHAR(10) & CHAR(10), sentArray,
{"Phrase 1.","Phrase 2.","Phrase 3.","Phrase 4.","Phrase 5.","Phrase 6.","Phrase 7.","Phrase 8.","Phrase 9.","Phrase 10.","Phrase 11.","Phrase 12."}, s, COLUMNS( sentArray ), pgha1, TEXTJOIN( " ", TRUE, IFERROR( SORTBY( INDEX( sentArray, 1, SEQUENCE( 1, p1Sent, RANDBETWEEN( 1, s - p1Sent ) ) ), RANDARRAY( 1, p1Sent, 0,1, FALSE) ), "" ) ), pgha2, TEXTJOIN( " ", TRUE, IFERROR( SORTBY( INDEX( sentArray, 1, SEQUENCE( 1, p2Sent, RANDBETWEEN( 1, s - p2Sent ) ) ), RANDARRAY( 1, p2Sent, 0,1, FALSE) ), "" ) ), pgha3, TEXTJOIN( " ", TRUE, IFERROR( SORTBY( INDEX( sentArray, 1, SEQUENCE( 1, p3Sent, RANDBETWEEN( 1, s - p3Sent ) ) ), RANDARRAY( 1, p3Sent, 0,1, FALSE) ), "" ) ), TEXTJOIN( pghDelimiter, TRUE, pgha1, pgha2, pgha3 ) )

You need to put in the how many sentences you want in each of the three paragraphs. These are p1Sent, p2Sent, and p3Sent. I set this to 3, 5, and 3.

Next, you need to tell it how to separate your paragraphs. I am double spacing here, so I set pghDelimiter to CHAR(10) & CHAR(10). For single spaced, you would set it to just CHAR(10).

Then you need to type in the phrases that you want it to choose from. You must put phrases in quotes, separated by commas and between the {} brackets. I made 12 here, but you can make as many as you want by adding ,"new phrase" before the last } bracket. The more phrases you make, the more random it will appear.

NB: Your phrases must be <= 255 characters long.

It should produce an output like this:

This will not repeat any sentences within paragraph, but it will likely repeat sentences between paragraphs. The more sentences you give it, the less it will repeat.

Now, here is an example with real content in Latin:

=LET( p1Sent, 3, p2Sent, 5, p3Sent, 3, pghDelimiter, CHAR(10) & CHAR(10), sentArray,
{"Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt explicabo.","Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt.","Neque porro quisquam est, qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit, sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaerat voluptatem.","Ut enim ad minima veniam, quis nostrum exercitationem ullam corporis suscipit laboriosam, nisi ut aliquid ex ea commodi consequatur?","Quis autem vel eum iure reprehenderit qui in ea voluptate velit esse quam nihil molestiae consequatur, vel illum qui dolorem eum fugiat quo voluptas nulla pariatur?","Habeo enim geminos a tergo, sic semper cum oculis meis ut transire mihi videre aliquis inclinatur ad prosequendum ea.","Et harum quidem rerum facilis est et expedita distinctio. Nam libero tempore, cum soluta nobis est eligendi optio cumque nihil impedit quo minus id quod maxime placeat facere possimus, omnis voluptas assumenda est, omnis dolor repellendus.","Temporibus autem quibusdam et aut officiis debitis aut rerum necessitatibus saepe eveniet ut et voluptates repudiandae sint et molestiae non recusandae.","Itaque earum rerum hic tenetur a sapiente delectus, ut aut reiciendis voluptatibus maiores alias consequatur aut perferendis doloribus asperiores repellat.","Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.","Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.","Unus pro omnibus omnes pro uno ac - helvitique confoederationis transire possit."}, s, COLUMNS( sentArray ), pgha1, TEXTJOIN( " ", TRUE, IFERROR( SORTBY( INDEX( sentArray, 1, SEQUENCE( 1, p1Sent, RANDBETWEEN( 1, s - p1Sent ) ) ), RANDARRAY( 1, p1Sent, 0,1, FALSE) ), "" ) ), pgha2, TEXTJOIN( " ", TRUE, IFERROR( SORTBY( INDEX( sentArray, 1, SEQUENCE( 1, p2Sent, RANDBETWEEN( 1, s - p2Sent ) ) ), RANDARRAY( 1, p2Sent, 0,1, FALSE) ), "" ) ), pgha3, TEXTJOIN( " ", TRUE, IFERROR( SORTBY( INDEX( sentArray, 1, SEQUENCE( 1, p3Sent, RANDBETWEEN( 1, s - p3Sent ) ) ), RANDARRAY( 1, p3Sent, 0,1, FALSE) ), "" ) ), TEXTJOIN( pghDelimiter, TRUE, pgha1, pgha2, pgha3 ) )

You can copy paste that directly into your workbook. It also has 12 phrases and 3,5,3 pgh sizes. And here is what that looks like.

If numbers are find Excel RAND function should help. If not this and you are comfortable using a bit of VBA, there is a section on this pages that generates random text, number and special character results.

Hopefully, this helps.