ABUSE Your Spreadsheet 🤯

CODING & SPREADSHEETS
The Palindrome Test

Since last we spoke I’ve…

1. had a cold courtesy of my sweet daughter 🤧 

2. not run a mile courtesy of my sweet cold 🏃‍♂️

3. edited a handful of previously recorded videos courtesy of my long backlog 📽️

Now to the sheets…

One of those videos is about palindromes. (That’s when a word is symmetrical - it’s spelled the same backward as forward…like CIVIC).

I came across an Excel challenge on YouTube where they walked through identifying palindromes using only built in functions and thought it would be fun to try my hand at it.

Turned out to be more difficult than I expected thanks to nesting multiple functions within each other and some creative techniques to reverse a word or phrase.

The video walkthrough I did is below 👇

If you want to follow along in the demo sheet I made, here it is.

The first part of the challenge was straightforward: ignore spaces, capitalization and punctuation. So, clean the data.

I used a combination of the LOWERCASE, REGEXREPLACE and SUBSTITUTE functions to get all the words/phrases cleaned up:

=SUBSTITUTE(REGEXREPLACE(LOWER(A2), "[[:punct:]]", "")," ", "")

Lowercase does exactly what it sounds like - puts everything in lowercase. Substitute takes all the spaces (“ “) and replaces them with nothing (““). Regexreplace uses the syntax “[[ :punct: ]]” to replace all punctation with nothing (““).

Deeper into the nests

From there, I used another combination of functions: LEN, SEQUENCE, MID, ARRAYFORMULA, JOIN and IF.

The bulk of the problem was finding a way to reverse the original word or phrase so that we can compare that reversed version with the first version.

So we need to step through a string one letter at a time and put it in a new cell.

I go through the explanation in more detail in the video above if you’re interested, but here is the end formula that accomplishes everything:

=IF(JOIN("",ARRAYFORMULA(MID(M4,
{SEQUENCE(1,LEN(M4),LEN(M4),-1)},1)))=M4,"It's a 
palindrome!","nope")

This is what allows us to, in one fell swoop, return either “It’s a palindrome!” or “nope”👇

But how does the reverse part work?

ARRAYFORMULA(MID(M2,{SEQUENCE(1,LEN(M2),LEN(M2),-1)},1))

ARRAYFORMULA lets us do an operation over a range, or in this case a sequence of steps.

So we’re grabbing the MID character at each step along the way.

The SEQUENCE and LEN combination lets us start at the position which is the length (LEN) of the string and then count backward by one until we get to the first character. The -1 is what lets us count backward in a sequence instead of forward.

That spits out each letter, as you can see below, into its own cell. Then, we come back in with JOIN to tie them together into one cell. Finally, we make our comparison using the IF statement.

Nothing to it, right?

Haha! Seriously, though, check out the video if you’re still scratching your head. It took me a fair amount of head scratching and googling to get all this to work and actually understand why it was working!

Thank you so much!

It means a lot that you’ve read this, and I hope it’s informed and/or entertained you for a few moments today!

Would love to say hi. Here are the best places to find me:

✉️ LinkedIn
📺️ YouTube

Ways I can help:

• View my spreadsheet and creative products on my Gumroad store.

• Need a powerful automation tool for Google Sheets? Try Coefficient to automatically import data and sync with your business systems.

• Hire an expert to help complete your next Google Sheets, Apps Script or Google Workplace project.