Classify a ledger line by keyword (SEARCH)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
The description to classify is in A2. Put "Travel" when it mentions fuel, "Rent" when it mentions rent, and "Other" otherwise, in the Account cell B2.
Match anywhere in the text, not just at the start, and ignore case.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | |
|---|---|---|
| 1 | Description | Account |
| 2 | Diesel fuel for the van | |
| 3 | Office rent, February | |
| 4 | Printer paper |
Notes
SEARCH finds a substring and ignores case; on a miss it raises an error, so ISNUMBER turns it into TRUE/FALSE. Testing from the most specific keyword to the least avoids classifying "rental car" as Rent when it should be Travel.
How to approach
Your answer goes in B2.
- Target cell is B2
- SEARCH returns a position or an error
- ISNUMBER makes that a TRUE/FALSE
- Shape: =IF(ISNUMBER(SEARCH(?, ?)), ?, ?)
Function guide
FAQ
How do I classify text by keyword?
=IF(ISNUMBER(SEARCH("keyword", cell)), "Category", "Other"), nested for more keywords. SEARCH ignores case; FIND does not.
Why does my classification miss some lines?
The keyword does not appear exactly as typed — a different word, a typo, or a trailing space. Review the unmatched lines and extend the keyword list.