Using a spreadsheet to process words

That synchysis experiment again

In How I tackled the synchysis style I explained how I produced a new version of “A bang on the head” that used the same words as the original to tell a completely different story. It took me five hours!

Mitchell Allen

thought a spreadsheet might have made life easier, and suggested we collaborated. So we each went off and did our thing, and here are the results. Download the spreadsheets, see what you think, and leave a comment.

Here’s a question you might like to consider: does using a spreadsheet produce a better result? My own view is as follows.

Looking at the results of these spreadsheets, I think where they could be useful is in giving someone a head start. For example, this randomised piece might be better than starting from scratch:

the different twilight, in states days and facing. semi-conscious nausea In concussion. people emerged was instead I and you, The a that up purposefully to I Accident one book. semi-somnambulance The wall. distancing” pleased “social much I than a the walked (if whom and into through awake), and mild was among touching next the quite middle I waited to went myself as After from for I thought I wore was knowledge serious twin door prevarication to Emergency, 17% and direction the who hour darkness few a As facing to secure not I failed night, of read a reason tried headaches. walking I the of you to was in writing, bathroom — that of out. the the where being half more my call can chin-warmer. I in through and do I much of but their to go have petrified the I woke result, nothing almost towards a entire door, the brought means An walk knocked masks later the being further had

What do you think?

I cheated a bit with mine. Although I used to be very adept at using Excel, I haven’t done much with it for years. I knew what I wanted to do, and had a fairly good idea of how to do it, but I asked ChatGPT to provide some solutions as well. I’ve had to save the spreadsheet below without programming code because Substack won’t allow me to upload what is known as a macro-enabled workbook. Therefore this version of the spreadsheet does the following, in essence:

1. Separates the words contained in the text in cell A1 — this text being the story of A bang on the head.

2. Rearranges them in a random order.

If you copy the text and the formula down into further rows, you get different versions of the text. You can use the text by copying the cell, such as B1, and then using the Paste as Values option in Excel or Word in order to get the text instead of the formula.

Eis By Terry No Macro

24.9KB ∙ XLSX file

Download

Download

Here’s the formula it uses:

=TEXTJOIN(" ", TRUE, SORTBY(FILTERXML("<a><b>" & SUBSTITUTE(A1, " ", "</b><b>") & "</b></a>", "//b"), RANDARRAY(COUNTA(FILTERXML("<a><b>" & SUBSTITUTE(A1, " ", "</b><b>") & "</b></a>", "//b")))))

This formula works by first using the SUBSTITUTE and FILTERXML functions to split the text into words. Then, it uses the RANDARRAY function to generate a random order for the words and the SORTBY function to sort the words based on this random order. Finally, it uses TEXTJOIN to combine the sorted words into a single text string.

OK that was my effort, in collaboration with ChatGPT. Now over to Mitch:

After Terry explained how he'd used Microsoft Word to track his progress with the synchysis experiment, I commented that my money was on his having used Excel. We chatted about thrown gauntlets and whatnot, with the end result being that we both challenged ourselves to wrangle synchysis programmatically.

Well, I didn't end up programming my worksheet. I had some ideas, but most of them remained at home. Here are the ideas that came out of the oven:

1. Terry's template, including paragraph spacing, was used as a display template. That's quite meta!
2. I seeded the word list by bonding each pronoun to whatever word it preceded. This is a neat way to preserve readability when one is spewing gibberish.
3. Following on from #2, I also bonded "social distancing" and Accident and Emergency.
4. Finally, I split these words (and bonded phrases) into separate rows and loaded them into an Excel Table.

Excel tables are lots of fun and, with a bit of trickery, one can perform all sorts of magical transformations, which is the essence of synchysis. Terry may be a bit too humble to brag, but his formulas were awesome, and I was impressed with how he did in ONE row, what took me three worksheets to accomplish!

I'm going to have to do some playing with FILTERXML and RANDARRAY. Those almost sound like hacker commands to siphon cryptocurrency from, err, never mind.

I had a blast playing with the text. It's kind of my jam and I appreciated having a chance to collaborate with Terry!

And here’s Mitch’s spreadsheet. Note that he did what I’m always telling others they should do, but didn’t in this case. He has provided an explanation in the spreadsheet itself of how it works.

Playing With Synchysis

113KB ∙ XLSX file

Download

Download

And if you’d like further explanation, here is what Mitch wrote. (This is for real propellor heads only. If you’re not one, and I don’t blame you, skip to Concluding Remarks.

Process

1. Copy template from Terry's Substack

2. Paste into plain text editor (EditPad)

3. Think about how to split the text (See Note A)

4. The text will be preprocessed in EditPad, because I can use Regular Expressions more easily.

5. After applying my "bonding" of articles and pronouns, split text into rows, using the space between words as the delimiter (See Note B)

6. Manually bond any phrases that should stay together (See Note C)

7. Paste the 134 rows into Excel table, column A

8. Generate Original Order in column B (1-134)

9. Generate Random Index (values between 100-999)

10. Devise trials to automate / track stages of synchysis (See Note D)

11. Annotate the trials

Try parts of speech switcheroo. Map the original.

Commonly listed English parts of speech are noun, verb, adjective, adverb, pronoun, preposition, conjunction, interjection, numeral, article, and determiner.

Due to lack of time and relative inexperience with linguistic analysis, I didn't attempt this.

It would have involved classifying the words (not hard, since there are just over 100); mapping the template to the classes and then using trials to rearrange the text while keeping verbs in the same place as they appear in the template; keeping the nouns in their same positions.

EXAMPLE:

Template

In the middle of the night, I woke up (if you can call being semi-conscious being awake), walked purposefully towards the door to go to the bathroom and almost knocked myself out.

Output

In the middle of a_chin-warmer, I_waited up (if you can call touching nausea being petrified), wore quite a few among the headaches to go to the masks - and almost brought myself days.

This plan doesn't handle idioms well...(knocked myself out)

Note A:

To maintain a semblance of flowing text, I'm thinking I should link each article and pronoun to the word the follows it.

the_middle, the_night, I_woke, etc.

Note B:

The regular expression to do this is as follows:

Text to find: \x20

Replacement text: \r\n

Note C:

After reviewing the rows, I decided to keep the following text together:

"social distancing"

Accident and Emergency

Note D:

For presentation purposes, I used a template and the camera tool. Annotations are to the right of the trial output.

Concluding remarks

Mitch and I would be interested to learn what you think of the spreadsheet method. Please leave a comment. Also, don’t forget to subscribe to Mitch’s fantastic puzzle newsletter: