Autocat Regex For Check Number

I’m trying to create a regex-based autocat for Google Sheets that will set the Transactions sheet Check Number column to the value of the Description column if the description only contains numbers - ex. 1234. It seems like it should be simple but I"m having trouble getting it to work.

Any help appreciated.

Thanks!

Mark

Hi Mark,

With the help of ChatGPT and some playing around I got the following formula to work if you paste it into the header row for the Check Number column. This is not using the autocat function from Tiller, it is using the ARRAYFORMULA in Google Sheets.

=ARRAYFORMULA(if(isblank(B:B), “”, if(row(B:B)=1,“Check Number”, IF(REGEXMATCH(C:C,“^\d+$”),C:C,“”)
)))

The formula first checks if the description field (in column C, ) only contains numbers by using the REGEXMATCH function with the regular expression ^\d+$. This regular expression matches any string that contains only one or more digits. If the description field only contains numbers, the REGEXMATCH function returns TRUE and the Check Number cell is set to the value of the description field.

If the description field contains only text or text and numeric values, the REGEXMATCH function returns FALSE and the Check Number cell is set to null.

If this looks like something that will work for you try it on a copy of your worksheet first. This is assuming that you do not put any manual check numbers into the Check Number cells because this will replace any values previously entered.

P.S. if your Description is in a different column - replace all the C:C with your column letters.

Fred

1 Like

Very cool! I’ll give that a try. Thank You!

BTW, I’m curious how you used ChatGPT to help. I’ve been following the buzz around it but haven’t interacted with it yet.

Mark

Let me know how it works for you. To use ChatGPT I just ask it to write the code for whatever I’m trying to do. The more detailed you state what you want the better the response. Once you start a conversation thread with ChatGPT you can continue the thread with additional questions or comments to get better results if the first response doesn’t get what you need. I even give it the error message Google Sheets gives me, if I get one, and ChatGPT responds with a correction if it can. The main thing is to be as specific as possible such as saying you want the formula to work in Google Sheets or Excel, etc…

It doesn’t always work but has been helpful to me so far. It gets me into the right ballpark of what I need to do even when it doesn’t get me exactly what I’m looking for.

Fred

Your sheets formula for check number worked! Only change I had to make was replacing the fancy curved double quotes (that pasting into chat most likely added) with regular straight double quotes. The ARRAYFORMULA function is very cool - had not seen it before. You You.