Spreadsheets

REGEXMATCH to categorise expenses by keywords in Google Sheets

Use REGEXMATCH in Google Sheets to tag bank narrations with a category from a list of keywords, then fill the whole column with one ARRAYFORMULA.

Editorial illustration for REGEXMATCH to categorise expenses by keywords in Google Sheets

Use REGEXMATCH with a pipe-separated list of keywords, and wrap the narration in LOWER so capital letters never cause a miss. =REGEXMATCH(LOWER(B2),"swiggy|zomato|dominos") returns TRUE when any one of the three words appears anywhere in the narration. Put that test inside IFS, give each test a category name, and you have a categoriser that reads your bank statement for you.

What does REGEXMATCH actually check?

REGEXMATCH(text, pattern) returns TRUE or FALSE. It does not need the whole cell to equal the pattern; it only needs the pattern to be found somewhere inside the text. The pipe symbol means "or", so one pattern can hold many keywords. That is the big advantage over SEARCH or FIND, which look for a single word at a time.

The match is case-sensitive, and bank narrations arrive in whatever case the bank chose. Two safe fixes: wrap the cell in LOWER and write every keyword in lower case, or start the pattern with (?i), as in "(?i)swiggy|zomato". Pick one style and stay with it.

How do I write the rule block with IFS?

Say your narrations sit in B2:B500 and you want the category in column C. Type this in C2 and copy it down for now:

=IFS(REGEXMATCH(LOWER(B2),"swiggy|zomato|dominos"),"Food delivery", REGEXMATCH(LOWER(B2),"uber|ola|rapido"),"Cabs", REGEXMATCH(LOWER(B2),"bigbasket|blinkit|zepto"),"Groceries", TRUE,"Uncategorised")

IFS reads its pairs from left to right and stops at the first TRUE. The last pair, TRUE with "Uncategorised", is the catch-all. Without it, any row that matches nothing returns #N/A, which then spoils every total that touches column C.

How do I apply it to every row at once?

Copying a formula down 500 rows works until you paste a new month below row 500. An ARRAYFORMULA in C2 handles the whole column, and the blank-row guard keeps unused rows empty:

=ARRAYFORMULA(IF(B2:B="","",IFS(REGEXMATCH(LOWER(B2:B),"swiggy|zomato|dominos"),"Food delivery", REGEXMATCH(LOWER(B2:B),"uber|ola|rapido"),"Cabs", TRUE,"Uncategorised")))

Delete the copied-down formulas below C2 first. If any cell under it holds something, Sheets refuses to spill and shows a #REF! asking you to clear the space.

Which pattern mistakes cause wrong categories?

Pattern problemExampleWhat goes wrongFix
Trailing pipe"swiggy|zomato|"The empty option matches every rowRemove the last pipe
Short keyword inside longer words"ola"Also matches words like "cola" or "pistola" in a narrationUse word edges: "\bola\b"
Special characters"amazon.pay"A dot means any single characterWrite "amazon\.pay"
Mixed case"Swiggy"Misses SWIGGY in the narrationLOWER the text, lower-case the keywords
Number cell as text inputAmount columnREGEXMATCH needs text and errors on numbersUse it on narration columns only

When does a keyword list stop being enough?

Rules by keyword are first-match rules. If "amazon" sits above "amazon pay electricity" in your IFS block, every electricity bill is filed as shopping. Put the narrower words higher, and keep an eye on the Uncategorised rows after each month's paste: those rows are your list of keywords still to add.

Around eight or ten categories, the formula becomes long enough that one wrong bracket breaks everything. At that point a lookup table is easier to maintain. If you would rather not maintain rules in a sheet at all, SpendixAI lets you set your own rules, learns from your corrections, and only uses an AI model for what is left if you switch that on.

A quick checklist before you trust the column

  • Filter column C for "Uncategorised" and read the narrations; add a keyword for each repeated one
  • Spot-check five rows of each category against the bank statement
  • Confirm no keyword is short enough to appear inside unrelated words
  • Check that the category names are spelled identically in every rule, since a stray space makes a new category in your summary
  • Keep a copy of the sheet before pasting a new month, so you can compare totals
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports