What Is Your Favorite AutoCat Regex?

Discussion
21

Alternative way to match two strings in any order (as AND logical operator).

foo.*bar|bar.*foo

I use it, for example, to categorize something under Subscriptions when it contains the name of the service AND the word “recurring”.

22

I would like to use this regex but make it case insensitive, so I can match with merchants that mix up their capitalization in a transaction description. For example, the regex would match Foo bar and Foo Bar.

If I do the regex (?i)foo.*bar|bar.*foo inside the =REGEXMATCH Google Sheets formula it works fine, but I get an error when I try it with AutoCat. Is there a way to write a regex for Tiller’s AutoCat to match two strings in any order (as an AND match) while being case insensitive?

23

What is the error message?

Also a note from the original post:

24

Error with (?i) added is:

Regex failed: "Invalid regular expression: /(?i)foo.* bar|bar.*foo/: Invalid group"

Doing foo.* bar|bar.*foo without (?i) does not match case insensitive for two terms. It just says no transactions found.

25

I’m curious what happens if the whole regex string is grouped?
(foo.* bar|bar.*foo)

The examples I’ve seen do that and that’s the way I’ve been doing it when using the | operator.

26

I have tested this, and the original regex is case-insensitive and should work as is.

1 Like
27

Yeah, same with my testing. case-insensitive, no grouping needed.

28

Grouping doesn’t fix it for me, but sounds like it isn’t needed. Am I putting the regex in the wrong column? I am putting it in “Description Regex” column on the AutoCat sheet. It throws an error when I put a bad regex in, so I’m assuming it is working. Sounds like something is wrong for me because it works for both of you.

29

Is the transaction categorized already? By default, AutoCat only runs on uncategorized transactions.

What else have you specified on the same AutoCat row? Other matching criteria? Are you specifying what AutoCat should do when the criteria is met - e.g. change Category and/or Description?

30

@Mark.S this is embarrassing, but the transaction I was trying to edit already had an AutoCat set and therefore the new line item with the regex wasn’t executing properly. Removing the other AutoCat entry made it work fine. Thanks for helping me think through this!

1 Like
31

I wanted to use Regex to categorize reimbursements to specific people… like Greg for instance. Sometimes it says reimburse, sometimes it says reimbursement. If I just use “reimburse” will that catch instances where it says “reimbursement”. if not can i add something to end of word reimburse so it matches reimbursement also?

Greg.*Reimburse|Reimburse.*Greg…

32

One more question! Can I add a third criteria for an AND regexmatch? I can have a variety of combinations of payments for certain property, certain team member, certain reason. Can I do this?

Cabin 1, Cabin 2, Cabin 3
Greg, Dave, Joe
Reimbursement, Labor cost, Bonus

Am I pushing the theoretical limits of physics? Will space fold in on itself?

33

Hi! Haven’t read through this whole thread, but in the fields being replaced can you use regex to keep something from the description sort of like a find and replace?

Allow me to give an exact example. I have various Doordash transactions that look like:

Dd Doordash Taqueriat
Dd Doordash Littlegre
Dd Doordash Freebirds
Dd Doordash Rodeogoat
Dd Doordash Mcdonalds
Dd Doordash Mcdonalds
Dd Doordash Whiterhin

I want to find those that match a pattern like: Dd\s+Doordash\s+(\w+)
Then rewrite it such that it is: Doordash - $1

Doordash -  Taqueriat
Doordash - Littlegre
...

Can this be done using autocat?

34

No, it’s just regex matching.

35

Newbie here, but loving this solution so I don’t have to roll my own. This topic caught my eye, because what?! I can use regex? A nerd’s dream. :slight_smile:

2 Likes
36

You could do this with separate lines in AutoCat.

  1. Match the Doordash Mcdonalds, and write a new description that is “Doordash - Mcdonalds”
  2. Match the Doordash Whiterhin, and write a new description that is “Doordash - Whiterhin”
1 Like
37

Hi, when I run AutoCat I get the following error: “Regex filter can only execute on strings and numbers.” The error message disappears in a few seconds and there’s no indication as to which regex failed. I have perhaps a dozen regexes in my rules. Any ideas on where I can start debugging?

38

How about duplicating your AutoCat sheet to have as a saved reference/restore and then binary delete blocks of stuff in the main AutoCat sheet, re-running AutoCat along the way, to narrow it down to the problematic cell?

I’m curious to hear what might be causing this, I haven’t been able to reproduce it yet :thinking:

2 Likes
39

New to Tiller, literally only a few days. :slightly_smiling_face:

This Regex feature was the deciding factor and I thought I would share a quick tip for those struggling with writing the Regex code.

A lot has changed since this thread was started in 2022 and while the sites listed here can be helpful, I have found AI to be very good at writing what I need.

The site I use is perplexity.ai and I give it a prompt similar to this:
" I need a Google Sheets Tiller AutoCat regex to match Square Inc Sqxx1217 / CompanyName Llc 123456789 Ach Credit. It should only be looking for Square Inc and CompanyName Llc since the other parts of the phrase could vary."

And it spits out the Regex which I paste in and viola, it works. (usually) :slightly_smiling_face:

5 Likes
40

Welcome to Tiller @drcbweaver Daryl. Great Regex AI tip!