To get the beneficiary and mode out of an IMPS narration, treat it as slash-separated segments, pull the segment you want with a formula, and add a Mode column using SEARCH. Then total by mode with a pivot or SUMIFS. Check how your own statement orders the parts first, because the layout below is invented for teaching.
What does an IMPS narration look like?
Take the invented example IMPS/412345678901/RAJESH KUMAR/okbank in A2. The first segment names the mode, the second is a reference, the third is a person's or business's name, and the last is extra text. Your bank may order these differently or add more segments, so copy a dozen real narrations and map them before you write formulas.
How do you pull a numbered segment?
There is a method that works in every version of Excel and needs no helper columns. It swaps each slash for a long run of spaces, then slices out one block. With n as the segment number:
- =TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",LEN(A2))),(n-1)*LEN(A2)+1,LEN(A2)))
- For the beneficiary, type 3 in place of n: =TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",LEN(A2))),2*LEN(A2)+1,LEN(A2)))
- For the reference, use 2: =TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",LEN(A2))),LEN(A2)+1,LEN(A2)))
Each slash becomes as many spaces as the whole text is long, so every segment starts at a predictable multiple of that length. MID takes one block, and TRIM removes the padding. Put n in a cell, say H1, and use (H1-1)*LEN(A2)+1 to make the formula reusable. If a row has fewer segments than n, the result is blank, so also run a slash count: =LEN(A2)-LEN(SUBSTITUTE(A2,"/","")).
How do you add a Mode column?
A mode column lets you compare IMPS, NEFT and UPI payments. Use SEARCH, which is not case-sensitive:
- =IF(ISNUMBER(SEARCH("IMPS",A2)),"IMPS",IF(ISNUMBER(SEARCH("NEFT",A2)),"NEFT",IF(ISNUMBER(SEARCH("UPI",A2)),"UPI","Other")))
Order matters. SEARCH finds the word anywhere, so a name that happens to contain those letters can trigger the wrong mode. If your mode always comes first, use LEFT instead: =IF(LEFT(A2,4)="IMPS","IMPS",...). That is stricter, and it avoids false matches inside names.
How do you total by mode?
With amounts in D and the mode in F, a SUMIFS gives the total for one mode, and a pivot gives them all at once.
| Mode | Formula for the total | Hypothetical total |
|---|---|---|
| IMPS | =SUMIFS($D$2:$D$500,$F$2:$F$500,"IMPS") | ₹18,400 |
| NEFT | =SUMIFS($D$2:$D$500,$F$2:$F$500,"NEFT") | ₹42,000 |
| UPI | =SUMIFS($D$2:$D$500,$F$2:$F$500,"UPI") | ₹9,650 |
| Grand total | =SUM(D2:D500) | ₹70,050 |
The invented totals add up: 18,400 + 42,000 + 9,650 is 70,050. For the pivot, select the range, choose Insert, then PivotTable, put Mode in Rows and Amount in Values, and set it to Sum. If any row lands in Other, check its narration.
What does the comparison tell you?
It shows where your money moves, which is useful for a reconciliation or for spotting that most of a month's outflow went through one channel. It does not tell you anything about fees or limits. Those depend on your bank, so check your own statement and the bank's published charges.
A useful follow-up is a second pivot with Month in Columns and Mode in Rows. It shows whether a channel is growing or fading from one month to the next. For instance, say IMPS totalled ₹18,400 in October and ₹6,000 in November, while UPI rose from ₹9,650 to ₹21,000. You would want to know why, and the narration, the amounts and the beneficiary names are where the answer sits. Keep the beneficiary column in the pivot filter, so you can click into a mode and see who received the money.
A short checklist
- Map the segments on a dozen real rows.
- Pull the beneficiary and reference with the numbered-segment formula.
- Add the Mode column, checking for false matches.
- Pivot by mode and check that the pivot total equals the statement total.
SpendixAI is a separate option if you would rather not maintain formulas: it imports statements you upload and lets you review each row before saving. For a one-off look at transfer modes, the sheet above is enough.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


