To clean an NEFT narration, first find how your own statement orders the pieces, because banks differ. Then use a delimiter formula to cut out the reference and the name, and drop the fixed prefix. The method below works on an invented layout with hyphens, and the same steps apply if yours uses slashes or spaces.
How do you find the structure in your file?
- Filter the narration column for NEFT and copy about twenty rows to a scratch sheet.
- Write down what appears first, such as the mode word, then the reference, then a name, then any free text.
- Count the delimiters in each row. Rows with different counts have a different shape.
- Check whether the same layout is used for money sent and money received. On a credit, the name may be the sender, and on a debit it may be the beneficiary. Look at both.
Do not copy a layout from a website, including this one. Use what your statement shows.
What does the worked example look like?
The invented narration is NEFT-N2810245678-VERMA TRADERS PVT LTD-INV 4471, held in A2. Its four parts are the mode, a bank reference, the beneficiary and a free-text note. You do not know in advance where each hyphen sits, so find them by number.
A neat trick is to replace the nth delimiter with a rare character, then find that character. SUBSTITUTE has an optional fourth argument, the instance number:
| Column | Formula in row 2 | Result |
|---|---|---|
| B (hyphen 1) | =FIND("~",SUBSTITUTE(A2,"-","~",1)) | 5 |
| C (hyphen 2) | =FIND("~",SUBSTITUTE(A2,"-","~",2)) | 17 |
| D (hyphen 3) | =FIND("~",SUBSTITUTE(A2,"-","~",3)) | 39 |
| E (reference) | =MID(A2,B2+1,C2-B2-1) | N2810245678 |
| F (beneficiary) | =TRIM(MID(A2,C2+1,D2-C2-1)) | VERMA TRADERS PVT LTD |
Check the arithmetic. The reference runs from position 6 to 16, so its length is 17 - 5 - 1 = 11 characters, which is the length of N2810245678. The name runs from 18 to 38, which is 39 - 17 - 1 = 21 characters, and VERMA TRADERS PVT LTD has 21 including spaces.
What if a name contains a hyphen?
Then every position after it shifts, and the formula returns a name that is cut short. Count delimiters first with =LEN(A2)-LEN(SUBSTITUTE(A2,"-","")). If most rows have 3 and a few have 4, set the odd ones aside and read them by hand. It is better to flag a hundred rows than to quietly misread them.
How do you remove the fixed prefix instead?
If all you want is the text after the mode word, a SUBSTITUTE is enough: =TRIM(SUBSTITUTE(A2,"NEFT-","",1)). The 1 means only the first match is replaced, so a name containing the same letters is left alone. This is useful for a quick, readable list but gives you the reference and note glued to the name, so the delimiter method is the proper clean-up.
What should you do with the reference?
- Store it as text, so a reference that starts with a letter or zeros is kept exactly.
- Use it to match against an invoice or another export. The same reference should appear on the payer's side as well.
- Keep it in its own column, not inside the name, so a filter on the name stays clean.
Before you trust the output
- Sort the beneficiary column and scan for rows that start or end with a stray character.
- Run LEN on the reference column and check the lengths agree.
- Total the amounts for NEFT rows before and after the clean-up. The sums must be equal.
- Keep the original narration in a column you do not edit.
Once names are consistent, group them with a pivot table to see how much went to each party. If you would rather start from a categorised view, SpendixAI can import a statement as PDF, CSV or Excel and lets you review the rows before they are saved.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

