# Using RegEx with AutoCat

**URL:** <https://community.tiller.com/t/using-regex-with-autocat/4797>\
**Category:** Google Sheets\
**Tags:** how-to, autocat\
**Created:** [July 16, 2020, 9:39pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797 "2020-07-16T21:39:01Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![wizavi](https://avatars.discourse-cdn.com/v4/letter/w/50afbb/32.png) [@wizavi](https://community.tiller.com/u/wizavi)\
**Post date:** [July 16, 2020, 9:39pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/1 "2020-07-16T21:39:01Z")

</div>

Hello - when using RegEx with AutoCAT - I am seeing issues.

My bank savings account transaction has entries name like for income entries

MYCOMPANY EDIPAYMENT PPD ID: 1212121212  
Mycompany Edipaymentppd Id: x33322

To cover both of these I was am trying to use AutoCAT RegEx entry

Category Description Contains Account Contains  
Paycheck MYCOMPANY.\*Edipayment Chase CHECKING

While the regex works in a text editor - I don’t see any transactions getting updated by AutoCat.

What am I doing wrong?

---

<div class="post-metadata">

**Author:** ![warren](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/warren/32/1191_2.png) [@warren](https://community.tiller.com/u/warren)\
**Post date:** [July 17, 2020, 2:13am UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/2 "2020-07-17T02:13:10Z")

</div>

You should be able to add a column name and append the regex suffix to it to build rules using Regex. E.g. Description Regex or Account Regex

Examples of terms here: [https://cs.lmu.edu/~ray/notes/regex/](https://cs.lmu.edu/~ray/notes/regex/)

---

<div class="post-metadata">

**Author:** ![wizavi](https://avatars.discourse-cdn.com/v4/letter/w/50afbb/32.png) [@wizavi](https://community.tiller.com/u/wizavi)\
**Post date:** [July 17, 2020, 2:38am UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/3 "2020-07-17T02:38:22Z")

</div>

I have used RegEx before. Also, what I am trying to do with RegEx is documented on StackOverflow as well. To verify that my RegEx statement is good, I copied some of my transactions data to Notepad++ which has RegEx search functionality, and I see that my RegEx string works as expected. So I am confident that the RegEx string is good.

But when I use it in Tiller AutoCAT, it isn’t working.

---

<div class="post-metadata">

**Author:** ![randy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/randy/32/16_2.png) [@randy](https://community.tiller.com/u/randy)\
**Post date:** [July 17, 2020, 2:45pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/4 "2020-07-17T14:45:45Z")

</div>

Hey @wizavi,

I’m the developer for the updated AutoCat implementation. I’m glad to hear you’re trying the Regex feature. I’m sorry to hear that your rules aren’t processing as expected.

Honestly, our focus has been on the more core/commonly-used aspects of AutoCat. The Regex capability was added as a late Easter egg with limited testing and documentation (thus far).

I think the issue is that the version of regex implemented in Google Apps Script doesn’t always adhere to the standard. An easy way to try regex within the context of how our add-on makes the calls is to open the **Script Editor** , manually run a function like the one below and then click `View / Logs` to see what processed.

```auto
function regexTest() {
  Logger.log('Best buy'.match(/(b|B)est\s*(b|B)uy.*/)!== null? 'match':'no match'); 
  Logger.log('Testing'.match(/ing$/)!== null? 'match':'no match'); 
}

```

These are the same calls we are making in our code. I know the process is iterative and clumsy, but you should be able to get where you need quickly until we can build out the regex tool’s documentation.

I hope this helps.  
Randy

---

<div class="post-metadata">

**Author:** ![wizavi](https://avatars.discourse-cdn.com/v4/letter/w/50afbb/32.png) [@wizavi](https://community.tiller.com/u/wizavi)\
**Post date:** [July 17, 2020, 3:54pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/6 "2020-07-17T15:54:01Z")

</div>

Thanks Randy for responding back - love it when devs are able to take time to interact with customers using their work.

_“I think the issue is that the version of regex implemented in Google Apps Script doesn’t always adhere to the standard.”_ - this feels scary to me and being a product guy I can imagine how it can impact larger audience. AutoCAT rules is something users would like to set and forget - so it is paramount that consistency over time is maintained. Maintaining consistency with non-standard stuff is next to impossible especially when you don’t own it.

If folks have to learn a special version of RegEx that is different from the standard - after a little while I will forget the quirks I learnt and any future issue will mean relearning and long term maintenance pain. Might be worth it to see if you can not depend on Google’s underlying functionality and use a standard library so that you own your destiny and it will also get you consistent interoperability between Google Sheets and Excel.

RegEx support is a very powerful feature - some might say how many users will really use it - they are right - but what they would be missing is that this is more important for template creators, and richer template library is the main reason I am evaluating Tiller over substitutes. With Money for Excel expanding, rich templates and active community is one way Tiller can differentiate your self and give higher value to customers.

Just my 2cents.

Thank you once again.

---

<div class="post-metadata">

**Author:** ![randy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/randy/32/16_2.png) [@randy](https://community.tiller.com/u/randy)\
**Post date:** [July 17, 2020, 4:15pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/7 "2020-07-17T16:15:59Z")

</div>

I agree that we have work to do to make regex easier.

I think better documentation with some examples will go a long way… and perhaps we can even add a regex tester into the rule builder interface where the user enters a rule and confirms that it triggers on a string.

Thanks for your feedback. With time, this feature will be stronger and easier to use.

Randy

---

<div class="post-metadata">

**Author:** ![wizavi](https://avatars.discourse-cdn.com/v4/letter/w/50afbb/32.png) [@wizavi](https://community.tiller.com/u/wizavi)\
**Post date:** [July 17, 2020, 5:06pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/8 "2020-07-17T17:06:09Z")

</div>

Thanks @randy for the detailed suggestion of using Script Editor.

Is there anyway to add RegEx modifiers when entering text in “Description Contains” cell?

---

<div class="post-metadata">

**Author:** ![randy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/randy/32/16_2.png) [@randy](https://community.tiller.com/u/randy)\
**Post date:** [July 17, 2020, 5:09pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/9 "2020-07-17T17:09:42Z")

</div>

No. You’d need to choose a Regex filter for that.

---

<div class="post-metadata">

**Author:** ![wizavi](https://avatars.discourse-cdn.com/v4/letter/w/50afbb/32.png) [@wizavi](https://community.tiller.com/u/wizavi)\
**Post date:** [July 17, 2020, 6:27pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/10 "2020-07-17T18:27:39Z")

</div>

Hi @randy - I just tried it out using Script Editor and I see successful matches - but when I use in Tiller sheets I am seeing “No Transactions updated”

```
 var str1 = 'MYCOMPANY EDIPAYMENT PPD ID: 2562526265'
 var str2 = 'Mycompany Edipaymentppd Id: x4442'

 Logger.log(str1.match(/(m|M)(y|Y)(c|C)(o|O)(m|M)(p|P)(a|A)(n|N)(y|Y).*(e|E)(d|D)(i|I)(p|P)(a|A)(y|Y)(m|M)(e|E)(n|N)(t|T)/)!== null? '1: Yes match':'no match');
 Logger.log(str2.match(/(m|M)(y|Y)(c|C)(o|O)(m|M)(p|P)(a|A)(n|N)(y|Y).*(e|E)(d|D)(i|I)(p|P)(a|A)(y|Y)(m|M)(e|E)(n|N)(t|T)/)!== null? '2: Yes match':'no match');

```

Here is screenshot of how I have it in my Transactions sheet

 ![image](https://us1.discourse-cdn.com/flex020/uploads/tillermoney/original/2X/9/957451bc47af329124b373d8e52131ea53dfea11.png)

Here is screenshot of how I have it in my AutoCAT entry (as you can see I put this rule at the top to not clash with any previous ones)

 ![image](https://us1.discourse-cdn.com/flex020/uploads/tillermoney/original/2X/5/55e3be3470c4527888af03b627c66ab1f424adbf.png)

But still seeing  
 ![image](https://us1.discourse-cdn.com/flex020/uploads/tillermoney/original/2X/5/5b8f4cec128bad68e13a64de26737e4823dc4458.png)

---

<div class="post-metadata">

**Author:** ![randy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/randy/32/16_2.png) [@randy](https://community.tiller.com/u/randy)\
**Post date:** [July 17, 2020, 6:40pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/11 "2020-07-17T18:40:27Z")

</div>

Try changing the column header in AutoCat from “Description Contains” to “Description Regex”, @wizavi. That will set your filter to a) operate on the “Description” column and b) use a “Regex” filter type.

---

<div class="post-metadata">

**Author:** ![wizavi](https://avatars.discourse-cdn.com/v4/letter/w/50afbb/32.png) [@wizavi](https://community.tiller.com/u/wizavi)\
**Post date:** [July 17, 2020, 7:08pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/12 "2020-07-17T19:08:47Z")

</div>

@randy Yup - that did it.

As I had other rules which needed “Description Contains”, instead of changing the column header I created a new column with header “Description Regex” and moved the expression into that - worked just as expected.

While I read the doc of AutCAT, I didn’t completely internalize the section _ **Filter Criteria Suffixes** _ of [https://help.tillerhq.com/en/articles/3792984-how-to-use-autocat-beta-for-tiller-money-feeds-sheets](https://help.tillerhq.com/en/articles/3792984-how-to-use-autocat-beta-for-tiller-money-feeds-sheets)

Thanks for clarifying. Might be helpful to have an example in the doc for a slowmind like me 🙂

---

<div class="post-metadata">

**Author:** ![wizavi](https://avatars.discourse-cdn.com/v4/letter/w/50afbb/32.png) [@wizavi](https://community.tiller.com/u/wizavi)\
**Post date:** [July 17, 2020, 8:51pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/13 "2020-07-17T20:51:24Z")

</div>

I am seeing many places where credit cards companies are NOT consistent with case sensitiveness of their text

```
Costco by Instacart Httpsinstacarca
COSTCO BY INSTACART HTTPSINSTACARCA

Www Costco Com x-x-2292 WA
WWW COSTCO COM 800-955-2292 WA

```

It might be really helpful to provide a way for users to provide RegEx modifiers - folks are already accustomed with the format of /_pattern_/_modifiers_ - why not just allow that notation?

Otherwise the regex strings will look horrendously bad and super hard to debug (see above) OR we have to create multiple rules to cover one for full upper case and one for full lower case, which will break if the descriptions ends with partial casing.

---

<div class="post-metadata">

**Author:** ![randy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/randy/32/16_2.png) [@randy](https://community.tiller.com/u/randy)\
**Post date:** [July 17, 2020, 9:42pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/14 "2020-07-17T21:42:33Z")

</div>

We have shared a beta of an AutoCat rule builder with early adopters. It doesn’t help much with regex but does layer on a UX for more intuitively creating AutoCat rule columns without having to understand the filter-column/filter-type paradigm.

---

<div class="post-metadata">

**Author:** ![randy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/randy/32/16_2.png) [@randy](https://community.tiller.com/u/randy)\
**Post date:** [July 17, 2020, 9:43pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/15 "2020-07-17T21:43:54Z")

</div>

The `Contains`/`Starts With`/`Ends With`/`Equals` filter types are case insensitive. That is probably a more efficient approach in most cases than regex.

---

<div class="post-metadata">

**Author:** ![randy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/randy/32/16_2.png) [@randy](https://community.tiller.com/u/randy)\
**Post date:** [July 17, 2020, 10:03pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/18 "2020-07-17T22:03:20Z")

</div>

I just changed the regex call in the add-on to function in a case insensitive mode. Right now, that change is pushed to a dev build that should be published broadly sometime next week. Thanks for flagging this issue. I think making the functionality case insensitive is the right solution…

---

<div class="post-metadata">

**Author:** ![wizavi](https://avatars.discourse-cdn.com/v4/letter/w/50afbb/32.png) [@wizavi](https://community.tiller.com/u/wizavi)\
**Post date:** [July 17, 2020, 10:07pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/19 "2020-07-17T22:07:44Z")

</div>

About AutoCat rule builder : How can I opt in for it?

Also - here is something I found to address the issue [https://sites.google.com/view/googlappsscript/hot-off-the-presses/regex-search-using-textfinder](https://sites.google.com/view/googlappsscript/hot-off-the-presses/regex-search-using-textfinder)

Case insensitive parsing makes sense - thanks for making the change so quickly - love it.

---

<div class="post-metadata">

**Author:** ![randy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/randy/32/16_2.png) [@randy](https://community.tiller.com/u/randy)\
**Post date:** [July 18, 2020, 5:26am UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/20 "2020-07-18T05:26:18Z")

</div>

My colleague, @tom, added you to the early adopter program, @wizavi. You should see a gray link at the bottom of the Tiller Money Feeds AutoCat sidebar pane. Let me know what you think.

---

<div class="post-metadata">

**Author:** ![ScottC](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/scottc/32/8764_2.png) [@ScottC](https://community.tiller.com/u/ScottC)\
**Post date:** [September 4, 2022, 12:45pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/21 "2022-09-04T12:45:08Z")

</div>

Randy:

I have the same issue mentioned by wizavi in his July 2020 post. For some reason, some of the transactions downloaded from Wells Fargo are upper case and some are lower. When I imported them , my AutoCat rules didn’t process.

Your post back to Wizavi in July 2020 says that the Contains/Starts With/Equals filter are case INSENSITIVE. Does this only apply to the Advanced AutoCat feature? if so, I missed that.

Also, regarding the use of Regex …

Another person in the Forum suggested using Regex by adding a column to AutoCat, “Description Regex.”

I’ve tested this in two ways:

Assuming this is the value in A2 being searched:

PURCHASE RETURN AUTHORIZED ON 08/29 PAYPAL \*DOLCE VITA 718-308-2314 NY

Method 1. Including the match value as a literal string in the Regex Expression

Formula: =regexmatch(a3,“(?i)(?:Paypal)”) which finds Paypal regardless of case but it requires adding the match string as a literal for every rule. I’d like to avoid this if possible.

Method 2. Referencing the Match Value from another cell - C9 in the example below. This evaluates as TRUE which is correct but, unfortunately, it also evaluates as True if C$9 is empty.

Formula: =regexmatch(A9,“(?i)(?:”& c$9 & “)”) where cell c$9 contains “dolce | vita | Paypal” (without quotes)

Can you tell me how to change the expression so it evaluates as FALSE if the reference cell is empty?

I suppose the real question is this. What is the best way to use AutoCat if the values in the Description field of the csv import file are a mixture of upper and lower case strings?

Thank for any all help.

ScottC  
Saluda, NC

---

<div class="post-metadata">

**Author:** ![jpfieber](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/jpfieber/32/4791_2.png) [@jpfieber](https://community.tiller.com/u/jpfieber)\
**Post date:** [September 4, 2022, 4:43pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/22 "2022-09-04T16:43:28Z")

</div>

Ahh, I think I see the problem. You don’t need to put a formula in the cell, just the Regex expression:  
`(?i)(?:Paypal)`  
Not sure if this fixes the issue you’re having, but I don’t think that column was intended to have formulas in it. @Randy will be authority on this, and likely knows a better way than I…

---

<div class="post-metadata">

**Author:** ![ScottC](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/scottc/32/8764_2.png) [@ScottC](https://community.tiller.com/u/ScottC)\
**Post date:** [September 4, 2022, 8:37pm UTC](https://community.tiller.com/t/using-regex-with-autocat/4797/23 "2022-09-04T20:37:54Z")

</div>

Joseph:

This is FYI. No need to reply but I wanted to let you know that, with your help, I _think_ I figured this out but still testing.

The whole point of this exercise is to create a rule that allows multiple values and that ignores the case. (UPPER, lower, etc.)

As you pointed out, I was trying to create a Regex formula when I didn’t need to if I used AutoCat’s Advanced Rule builder and selected Regex from the top Filter Criteria. Then, I entered multiple values separated by the pipe character ( | ) into a single match criteria.

![CleanShot 2022-09-04 at 16.34.09@2x.png](https://us1.discourse-cdn.com/flex020/uploads/tillermoney/original/2X/f/f5cce53744538af72dcd5b6182c9ba8399897d79.png)

Tiller added the “Description Regex” column in the AutoCat tab and added the example values shown below. (I hope you can get screenshots).  
 ![CleanShot 2022-09-04 at 16.31.42@2x.png](https://us1.discourse-cdn.com/flex020/uploads/tillermoney/original/2X/9/987d94686a03c1ec5f8eb97e73cba4afadc9f567.png)

This seemed to work but I haven’t tested thoroughly.

I hope to connect with Randy and / or find more written docs about using Regex because I think it would greatly simplify the Categorization process. Tiller is already way better than others that I’ve tried but this would make it even better.

Thanks very much for your help.

ScottC  
Saluda, NC

[Next page](https://community.tiller.com/t/using-regex-with-autocat/4797.md?page=2)