How to create a grawlix generator in Excel

Blog post views certainly follow a Pareto distribution, with a handful of posts getting the disproportionate attention and the rest sentenced to lurk in the shadows:

For whatever reason, one post that keeps getting viewed is how to generate an expletive string in R:

Now I know that not everyone’s using R (but if you’d like to learn, please check out my book Advancing into Analytics). So I decided to try rebuilding this in Excel. Turns out that with the help of dynamic arrays, this problem becomes pretty %^$@ easy to solve (and yes, that will be a running gag of this post)…

What the @%!& is a grawlix?

Turns out, this is the formal name for that expletive string.

How the &^@! do I build it in Excel?

Direct… I like it! Let’s go for it:

Our basic steps for this task:

1. Generate all the possible Excel characters to include in the grawlix
2. Select which characters to include in the grawlix
3. Build a grawlix that concatenates values from the “grawlix bank” at random.

It all starts with CHAR()

CHAR() is a lesser-known Excel function that will return the corresponding Excel character for all numbers between 1 and 255.

Now, you could use the flash fill to expand a list of numbers from 1 to 255. But we’ll take a different path here.

Dynamic arrays make this so &%^! easy

Almost everything we do in this demo is powered by dynamic arrays. If you’re new to the topic, I suggest checking out Alan Murray’s Advanced Excel Success. You can check out my review of the book here.

From 255 possibilities to a few %$!%* options

In column A, we’ll list all possible character numbers from 1 to 255 with the SEQUENCE() function:

=SEQUENCE(255, 1)

From there we’ll feed the values from column A into column B:

=CHAR(A2)

Now in column C, you can flag any of the resulting 255 characters TRUE to include in our so-called “grawlix bank.” If you really wanted to get fancy, you could add data validation or other protections here.

Your worksheet so far should look like this (or a little different if you picked different characters):

Assembling the ^#%@ grawlix bank

To make it clear which characters were flagged TRUE, we’ll use the FILTER() function: specifically, we want to select all the values in column A where the corresponding value in column C is marked as TRUE.

I’m going to use the OFFSET() function here to specify which ranges I want to filter on, relative to the dynamic array starting in cell A2. The # next to A2 is the “spill operator” indicating we’re referring to the dynamic array, not the individual cell.

=FILTER(OFFSET(A2#,,1), OFFSET(A2#,,2)=TRUE)

At this point, your worksheet should look something like this:

Making the actual %$^@ grawlix

All right, now to slap these together at random and make the grawlix. In cell H2, we’re going to add a place for the user to specify how many characters they want included.

Beneath that, in cell G7, let’s list out the numbers from 1 to whatever the user specified. We can do that using the SEQUENCE() function:

=SEQUENCE(H2, 1)

Now this next formula is admittedly a bit of a doozy. Effectively what we want to do is have a random character drawn in column H wherever there’s a number in column G, i.e. where it’s not blank. That random character will be drawn via an index number from a row number selected at random. Ready? Put the following in H7:

=IF(ISBLANK(G7),"",INDEX($E$2#,RANDBETWEEN(1,ROWS($E$2#)),1))

Your worksheet should now look something like this. We are getting close!

Now that we’ve generated the sequence of characters to put in the grawlix, all that’s left is to paste them all together into one cell. We will do that with the CONCAT() function, again using the OFFSET() function to make the cell reference relative to the dynamic array. That way, we can refer to a dynamic rather than static range:

=CONCAT(OFFSET(G7#,,1))

Pretty ^$#@ cool, right?

Here’s the final product:

I mean, it’s nothing you couldn’t have done on your keyboard alone. And there are grawlix generators in abundance online. But I found this a worthwhile exercise to learn a bit about dynamic arrays, random numbers, the CHAR() function and more.

What do you think? Where are you using dynamic arrays in your work? Let me know in the comments.