Forum Discussion
Put a specific word in a cell based on the word in another cell
Hey everyone, I need help automating the categorisation of my bank statement in excel. I do not have microsoft 365.
I want excel to look for specific words from my transactions eg Woolworths or McDonalds and then based on that, put another word into the categories cell. For Woolworths I want it to populate groceries, for McDonalds I want it to populate Eating Out. Etc.
I would really appreciate the help and in the most basic way possible. I only use basic formulas, so array formulas are quite foreign to me. I have tried googling and nothing seems to work quite right.
Happy to move columns around, but would prefer to keep Date, Amount & Description next to each other as that is the order from my bank statement download. I also don't need the Merchant / Category section in notes, but previous attempts led me to adding them.
Thanks
3 Replies
I have fleshed out some possible solutions in this workbook on Microsoft OneDrive: 2026-05-02 JJM categorize expenditures by keywords.xlsx The _Info worksheet contains explanations and references.
You absolutely can do this without Microsoft 365, without array formulas, and without stress.
In cell D2, put this formula:
=IF(ISNUMBER(SEARCH("WOOLWORTHS",C2)),"Groceries",IF(ISNUMBER(SEARCH("MCDONALDS",C2)),"Eating Out",IF(ISNUMBER(SEARCH("PRIME",C2)),"Streaming","")))
Then drag down column D.
How to add more rules
Just replace the final "" with another IF(ISNUMBER(SEARCH("WORD",C2)),"Category", and close with "" at the end.
Example with 5 rules:
=IF(ISNUMBER(SEARCH("WOOLWORTHS",C2)),"Groceries",IF(ISNUMBER(SEARCH("MCDONALDS",C2)),"Eating Out",IF(ISNUMBER(SEARCH("PRIME",C2)),"Streaming",IF(ISNUMBER(SEARCH("AMAZON",C2)),"Shopping",""))))
friendly tip…
If a transaction doesn't match any word, the cell will stay blank. Just manually type the category for those few — or add another rule if you see a pattern.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
How about a lookup table of keywords and categories, by using formulas like VLOOKUP or INDEX/MATCH combined with SEARCH:
https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
https://exceljet.net/formulas/categorize-text-with-keywords
https://www.extendoffice.com/documents/excel/7144-excel-categorize-bank-transactions.html
