What Is Your Favorite AutoCat Regex?

The powerful, highly-customizable categorization-engine AutoCat is one of the features that makes Tiller unique.

Many users have built filters using familiar criteria like Contains.
More advanced users have experimented with filters like Starts With, Ends With or Max and Min.
But the Regex filter stands apart for only the most adventurous.

Regex is short for regular expression. It’s kind of a programming language for pattern matching, making it a great fit for complex AutoCat filters that cannot be realized with basic rules.

A warning before we delve deeper…! Regex filters can be fussylike really fussy!— and frustrating. If you don’t have the time, patience or persistence to run them to ground, I recommend sticking with the basic filter types. If you need help with a regex filter, ask if someone in the community can help.

There are some great tutorials on the web— like this one or this one— that go through the basics of regex. Essentially, a special set of anchors, quantifiers and special characters allow you to encode pattern matching logic.

Regex Examples

  • til{2}er - contains the word “tiller”
  • (feeds|tiller money) - contains “feeds” or “tiller money”
  • (amazon|amzn|kindle) - contains “amazon” or “amzn” or “kindle”
  • ^Transfer - starts with “transfer”
  • Transfer$ - ends with “transfer”
  • (^|\s)(Sat|Saturday|Sun|Sunday)($|\s) - contains " Sat " or " Saturday " or " Sun " or " Sunday " with either leading/trailing whitespace or start-of-line/end-of-line

Do You Regex?

Let us know if you use regex filters with Autocat!
If so, what is your favorite regex filter application?

Notes

  • Tiller’s regex filter criteria are case insensitive.
  • Regex is only supported for AutoCat for Google Sheets at this time
3 Likes

This site lets you build and test expressions:
RegExr: Learn, Build, & Test RegEx

1 Like

This site is also great for experimenting with regex, @yossiea.

Good post @randy !
Yes, Regex can be frustrating and fussy. But once you get it working, it can be very powerful.

I want to add a little on how you use Regex in Google sheets and Tiller.

There are 3 named functions:

  1. =REGEXMATCH(text,regular_expression) - which tests whether some text matches the regular expression
  2. =REGEXEXTRACT(text, regular_expression) - which extracts text according to regex
  3. =REGEXREPLACE(text,regular_expression,replacement) - which replaces a text string using regex.

I’ve linked the function names to the Google Sheets documentation pages.

Jon

3 Likes

I have not used Regex’s in AutoCat… I’m still in the ManualCat phase of getting comfortable with reporting. As a programmer, I firmly believe in doing something manually until I understand it well enough to automate it. Doing the wrong thing faster is an anti-pattern!

That being said, here’s my favorite Regex…

Background: My data is patched from many sources (e.g. mint, CSV’s from institutions, Tiller). I put it all in the sheet, but since there are overlapping periods, there are duplicates. I don’t like deleting data because that cannot be undone, so I choose instead to augment it with fields that would let me change the logic for which sources I prefer for which time periods. I use an array formula to calculate Source data, then hand-fill a Preferred Source based on manual analysis. My queries (or pivot tables) all have logic to select only the rows where those values are equal. This has saved my bacon when I find flaws in my analysis. Since I have not deleted the rows, I need only update the Preferred Source field to “swap” the good for the bad. Notice, particularly, that this cascaded logic has a default that is a division-by-zero error. I like to fail very visibly!

={"Source"; ArrayFormula( if(REGEXMATCH($O2:$O,"^[^\w]+BECU"), "BECU", if(REGEXMATCH($O2:$O,"^[^\w]+Citi"), "Citi", if(REGEXMATCH($O2:$O,"^[^\w]+mint"), "mint", if(REGEXMATCH($O2:$O,"^[^\w]+Umpqua"), "Umpqua", if(REGEXMATCH($K2:$K,"\A[[:xdigit:]]+\z"), "Tiller", if(REGEXMATCH($K2:$K,"\Asplit:[[:xdigit:]]+\[\d+\]\z"), "Tiller Split", 1/0 )))))))}

P.S. It’s a shame that the preformatted text feature of this Wiki-like field reformats my nicely preformatted text! The formatting adds a lot to the readability of the code.

There’s more than one way to skin a cat… Here’s a screenshot of the well-formatted code:

  • Range $O2:$O is metadata, filled in by some automation that I forget (but that the code remembers).
  • Range $K2:$K is Transaction ID. Note that the Transaction Splitter reformats this from all hex-digits, as indicated by the different regexp’s.

Enjoy!

1 Like

I used to hate nested if statements in languages like this (or ternary operators in C-like languages), but once I learned how to format them like this, it reads very nicely as lines of predicate → result, with a default value dangling at the end.

value = predicate1 ? value 1 :
longerPredicate2 ? longValue2 :
shortPred3 ? val3 :
defaultValue;

Screen Shot 2022-09-17 at 10.25.34 PM

I like to say, there’s more than one way to climb a tree :slight_smile:

Skinning cats sounds so grotesque.

But thank you for sharing your Regex examples. I am not a programmer nor do I understand Regex so when these questions come into our primary support channel I am always :exploding_head: so it’s nice to finally have a better reference to point people to :slight_smile:

1 Like

I reallly like that. I’ll start using it. And yes… the cat image is not great.

And… I like climbing trees (not skimming or choppint). Good call.

On a similar note. y partner Wendy, upon becoming a vegetarian traded “Eat the fish. Spit out the bones.” to “Eat the nuts. Spit out the shells”.

Hmmmhhh… Regarding the Regexp’s, I thought I was replying to a comment in the community channel. Being somewhat of a Regex junkie, I thought I could move that conversation forward for whoever started it (and I figured it was a Tiller employee trying to generate some chat). If it didn’t go to the whole commnunity, please let me know. That was my intent.

Best,
Michael

P.S. Feel free to @-reference me on tough Regex questions. I love those kinds of puzzles and would value the opportunity to share Regex wonderfulness.

2 Likes

Thanks, @michaelrwolfseattle great to have someone knowledgable to point folks to here.

@morgan here at Tiller came up with “Feeding two birds with one scone” for me when I was stumped on how to rework “the two birds with one stone” one :wink:

Thanks

1 Like

@michaelrwolfseattle , if you use the ` from
image
you can type code without having the text get messed up.

@heather , you can always “Download two accounts with one connection.”

1 Like

Having been gluten-free for years, I was elated to find a Gluten-Free Scone Mix!!! I put cranberries in them and ginger preserves on them.

Yum!!

2 Likes

I tried the preformatted text two ways:

  • with back-tick (as @yossiea suggested)
  • clicking on </> icon in tool bar

They both consumed leading spaces, thus deleting the visual outline that I’d created with indentation.

I needed an autocat rule to match TWO words in any order in a description. The autocat rule generator does not support AND operations on string matches, only ORs. Here is the regex to match two words in a description:

^(?=.\banana\b)(?=.\apple\b).*$

This will match any transaction where the description contains both banana AND apple.

3 Likes

This is really good. My payroll has a dynamic date in the definition and I also get reimbursement from them, so I need to use company and payroll, so this should do the trick.

Hi, could you please post some examples as to where (which columns) in Autocat sheet you would enter some sample regex?

Just create a new column called “Description Regex” and you can try your Regex formulas on the Description column, @diogo6.

1 Like

Got it, will do. That seems to work for when you’re looking for a match but what about for the renaming of a given column… let’s say for example if I wanted to always strip away: “[Amazon Item]” from the imported transactions’ Descriptions… like here:

I guess I’m ultimately asking if Regex can also be used to leverage the “deliverable columns” as opposed to the triggering ones?

Are you asking if the output rule can incorporate dynamic data from a transaction it is modifying? Unfortunately, that is not possible at this time, @diogo6.

2 Likes

Yep, that’s what I was asking and “too bad, but ok, that’s what I’d thought.” If you can add that to your long to do list, it’d be great :slight_smile: