Bank statement narrations arrive with double spaces, invisible characters and shouting capitals, which is why a lookup for "Grocer" misses "Grocer " with a trailing space. One helper column with SUBSTITUTE, CLEAN, TRIM and UPPER (or PROPER) turns every narration into a consistent label you can match, count and group.
Which characters cause the trouble?
- Extra spaces at the start, end or in the middle. TRIM removes these.
- Line breaks and other non-printing characters. CLEAN removes these.
- Non-breaking spaces, character 160, common when text is copied from a PDF or web page. TRIM does not touch these, so SUBSTITUTE has to.
- Mixed capitalisation. Excel matches text case-insensitively in most lookups, but a clean look helps your eyes and any export you send.
What is the one-column pipeline?
With the raw narration in column B, enter this in column E and fill down:
- =TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," ")))
Read from the inside out: swap each non-breaking space for a normal one, remove unprintable characters, then collapse the rest of the spacing. To shout in capitals, wrap it in UPPER; to get a tidy mixed case, wrap it in PROPER:
- =UPPER(TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))))
- =PROPER(TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))))
| Raw narration | After the pipeline (PROPER) |
|---|---|
| BIG BAZAAR | Big Bazaar |
| kirana store mumbai | Kirana Store Mumbai |
| DOMINO'S PIZZA | Domino'S Pizza |
| UPI payment | Upi Payment |
The last two rows show PROPER's limits: it capitalises the letter after an apostrophe and does not know that UPI is an acronym. Use UPPER for narrations, which avoids both surprises, and keep PROPER for a final display column.
How do I remove reference numbers from narrations?
Many narrations carry a long number that changes on every transaction, so no two rows of the same merchant match. Check how your own bank writes it first. In Google Sheets, REGEXREPLACE can strip runs of digits:
- =TRIM(REGEXREPLACE(B2,"[0-9]{6,}",""))
This deletes any sequence of six or more digits. Excel does not offer REGEXREPLACE in every version, so there you can use Find and Replace with wildcards, or Text to Columns if the narration is separated by slashes and the merchant sits in a fixed position. Always test on a few rows from your own statement, since layouts differ.
How do I test that the cleaning worked?
Compare lengths. This flags cells that still had stray spaces in the original:
- =LEN(B2)<>LEN(TRIM(B2))
For duplicate checking, count identical cleaned rows with the same date and amount:
- =COUNTIFS($E$2:$E$500,E2,$A$2:$A$500,A2,$D$2:$D$500,D2)>1
TRUE means the same cleaned merchant, date and amount appear more than once. It can be a double charge or a genuine repeat purchase, for example two identical tea stall payments in a day, so look before deleting.
You can also strip fixed prefixes that appear on every narration, such as a payment-mode label followed by a hyphen. Look at your own statement to see what repeats, then add one SUBSTITUTE for each, for example =SUBSTITUTE(E2,"UPI-",""). Beyond three or four of these, keep the prefixes in a small table and process them in a second helper column, rather than nesting a long chain. When the cleaned column looks right, copy it and paste as values, so later edits to the raw column do not shift your results.
Excel also has a formula-free option. Power Query, found under Data, From Table/Range, has Format commands for Trim, Clean and capitalisation, and it repeats the same steps when you refresh with a new statement. It takes longer to set up than a helper column, and pays off only if you do this every month.
A simple lookup after cleaning
Build a small mapping table of cleaned merchant name to category, then pull it in with =IFERROR(VLOOKUP(E2,$H$2:$I$200,2,FALSE),"Review"). Anything unmatched says Review, and you add it to the table once. Over a few months, the table covers most of your regular spending. SpendixAI does something similar with your own rules and merchant history, and lets you add merchant aliases.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


