Linking Cells Containing the Same Partial Text

This is a quirky one.

I’ve got a text column I want to compare against itself. It’s all short text.


Safari Hat
Fly fishing
Grey Mouse
Mouse Trap

I would like to search this column and find partial text at the end that matches partial text at the beginning of another cell (and vice versa). So Butterfly matches with Fly fishing. If it could dynamically link these matches, great. I can count from there. If it just counts them, that works too. In this scenario, Butterfly should match with Fly fishing, and Fly fishing should match with Butterfly. If something else ended in “butter”, that’d count towards Butterfly. Make sense? If this is too weird and there is no easy answer, I totally get it. It would just help with a fun way to represent data that I’m toying with. Many thanks.

I imagine this would be a vlookup with wildcards in Excel, but I’m not a master of that either.
But maybe it’s a ‘find’ or something else here?

I can’t think of a way to do this. Keep in mind that while Airtable’s grid view looks a lot like a spreadsheet, it’s structurally very different because it’s a database. Records (rows) have no knowledge of each other, at least not natively. There are some table tricks you can use to do certain types of cross-row comparisons, but what you’re looking to accomplish is actually fairly complex. You’re trying to match a character sequence of unknown length between an unknown number of records. If the character sequence length were fixed—i.e. matching the last five characters with the first five characters—there may be certain ways to pull off a variation of that idea, but even there it would be very limited. Opening it up to try to find matches of any length is way more complex.

I’m all for weird, but even with the tricks that I know can kinda-sorta work, they will only just barely work in the right circumstances. Sorry.

Thanks for taking the time to think about my dilemma.

I may just have to approach it like a database instead. Or do it manually. I am beginning to realize how very complex this thing could get.