Card swipe narrations carry a lot of noise around the merchant: a prefix like POS, a terminal or store code, and a city. You can strip it with a small substitutions table that lists each piece of noise once, and a formula that removes every listed item. That gives you one consistent merchant name per shop.
What does the noise look like?
These invented examples show the problem. The same sweet shop appears three ways, so a pivot would treat it as three merchants:
| Raw narration | After cleaning |
|---|---|
| POS 40021177 ROYAL SWEETS CHENNAI | ROYAL SWEETS |
| PURCHASE ROYAL SWEETS*T4417 MUMBAI | ROYAL SWEETS |
| POS ROYAL SWEETS CHENNAI IN | ROYAL SWEETS |
Your statement will differ. Look at fifty rows and list what repeats: the leading words, whether a code is attached with an asterisk, and the place names. Check them on your own file.
How do you build the substitutions table?
Make a second sheet with one column called Remove. List each piece of noise on its own row, for example POS, PURCHASE, IN, CHENNAI, MUMBAI. You will add to it as new cities appear. Keep every entry in capitals, because SUBSTITUTE, unlike SEARCH, is case-sensitive.
Which formula removes everything on the list?
- In Excel 365, =TRIM(REDUCE(UPPER(A2),Subs!$A$2:$A$60,LAMBDA(acc,x,SUBSTITUTE(acc,x,""))))
- In older versions, nest SUBSTITUTE for the few entries that matter most: =TRIM(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"POS ",""),"CHENNAI",""))
REDUCE starts with the upper-cased narration, then runs SUBSTITUTE once per entry in the list, each time on the result of the last. Blank cells in the list are harmless, because substituting empty text changes nothing. One hazard: SUBSTITUTE removes the text wherever it appears, so a noise word like IN would also be cut from the middle of INDIA SWEETS. Use longer fragments, such as " IN", with a leading space, or accept that a flagged review is needed.
How do you handle codes joined with an asterisk?
When a merchant name and a code are joined by an asterisk, the name is the part before it. This formula keeps it, and the added asterisk ensures that a row without one still works:
- =LEFT(A2,FIND("*",A2&"*")-1)
- For PURCHASE ROYAL SWEETS*T4417 MUMBAI it returns PURCHASE ROYAL SWEETS. Run the substitutions after this to drop the leading word.
Terminal numbers are harder. If they are always the first word after POS, a rule like "remove the second word when it contains digits" is the tidy way to catch them, but test it, since some shop names contain numbers legitimately. Consider listing codes by hand only if there are just a handful.
Cities deserve a second thought. A name that ends in a city can be useful when the same chain has outlets in two places and you want to know which one you used. If that matters, copy the city into its own column before you remove it. With a city list in Subs!$B$2:$B$40, free of blank cells, =IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH(Subs!$B$2:$B$40,A2)),Subs!$B$2:$B$40),"") returns the last city found in the narration. Then clean the merchant column as before. You lose nothing, and you can still pivot by place.
How do you check you did not remove too much?
- Filter for blank results. A row that was entirely noise now has no name.
- Compare the count of unique names before and after.
- Check that the total amount is unchanged. Cleaning should never alter it.
- Spot-check names that contain a listed word, such as a shop called PURCHASE CENTRE.
Where does this stop being worth it?
A substitutions table is quick to start and slow to maintain, because every new city or terminal format needs a new entry. If you notice yourself editing it weekly, an alternative is to let a tool learn from your corrections. SpendixAI normalises merchant names, lets you add aliases and turns your corrections into history, though it is wise to check how it handles your own statement.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

