To standardise merchant names, build a two-column table: a fragment that appears in the messy text, and the clean name you want. Then use a lookup formula that finds which fragment sits inside each narration and returns its clean name. Keep adding rows whenever a new spelling turns up, and the sheet gets better with use.
What does a mapping table look like?
Say one dairy appears in five forms in your statement. Everything below is invented to show the idea:
| Raw narration (before) | Fragment in the table | Clean name (after) |
|---|---|---|
| ANAND DAIRY BLR | ANAND DAIRY | Anand Dairy |
| UPI/412345678901/ANAND DAIRYY/anandd@okbank | ANAND DAIRY | Anand Dairy |
| POS ANAND DAIRY PRODUCTS | ANAND DAIRY | Anand Dairy |
| anand dairy and sweets | ANAND DAIRY | Anand Dairy |
| ANANDDAIRY 23 | ANANDDAIRY | Anand Dairy |
Notice that two fragments are needed, one with a space and one without, and the doubled letter in DAIRYY still contains ANAND DAIR. The fragment can be shorter than the full name, which is how one entry catches many spellings. Keep it long enough not to catch other shops.
Which formula looks the fragment up inside the text?
A normal lookup finds a value that equals another. Here the fragment is only part of the narration, so you need a formula that tests every fragment against the narration. Put the fragments in F2:F200 and the clean names in G2:G200. This one works in every version of Excel:
- =IFERROR(LOOKUP(2,1/((F$2:F$200<>"")*ISNUMBER(SEARCH(F$2:F$200,A2))),G$2:G$200),"Unmapped")
SEARCH tests every fragment against the narration in A2. ISNUMBER turns each result into TRUE or FALSE, and dividing 1 by that gives a number for matches and an error for misses. LOOKUP with 2 skips the errors and returns the last match. The check for non-blank fragments is essential: SEARCH on an empty cell matches everything, so unfilled rows at the bottom of your table would claim every narration.
In Excel 365 you can use the first match instead, which is easier to reason about: =XLOOKUP(TRUE,(F$2:F$200<>"")*ISNUMBER(SEARCH(F$2:F$200,A2)),G$2:G$200,"Unmapped"). Because the result is the first match, the order of rows in your table is the order of priority.
How do you keep the table growing?
- Filter your cleaned column for Unmapped.
- Copy the narrations that remain to a scratch area and pick the shortest fragment that identifies the shop.
- Add one row to the table, with the fragment and the clean name.
- The formulas update at once, and the Unmapped count drops.
Each pass takes a few minutes, and the biggest merchants clear first. A table with forty rows can handle most of a household's regular spending.
Should the clean name come from a typed list?
Yes, if you plan to pivot on it. Type each clean name once and always copy it, because Anand Dairy and Anand dairy are different values to a pivot table. Set up a drop-down list with Data Validation for the clean-name column, so a typo cannot slip in.
How do you check the result?
- Count the unique clean names before and after the lookup.
- Sort by clean name and scan for pairs that look like the same shop.
- Confirm that the total of the amounts is unchanged.
- Keep the original narration column so every value can be traced.
One rule to keep in mind: the fragment must appear in the narration exactly, apart from case. Extra spaces inside a name, such as two spaces between ANAND and DAIRY, will stop ANAND DAIRY from matching. Run TRIM on the narration first, either inside the formula or in a helper column, and search that instead.
SpendixAI works along similar lines: it uses a normalised merchant name, lets you add merchant aliases and treats your corrections as history. If keeping the table by hand feels like too much, that is the point where an import-and-review approach may suit you better.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


