Bank Statement Lesson 4: Read UPI Narrations (Payee, UPI ID, Reference)

Bank Statement Lesson 4: Read UPI Narrations (Payee, UPI ID, Reference) 1

📎 This article includes 1 downloadable practice file ↓

⏱ 2 min read

📘 Bank Statement Automation Course · Lesson 4 of 8

Advertisement
In this article
  1. Pull out each part
  2. Common mistakes
  3. Practice

Most of a modern Indian statement is UPI. Each narration packs several fields separated by slashes:

UPI/DR/412345678901/SWIGGY/YESB/swiggy@ybl/Payment
 1   2       3        4     5       6        7
Part Meaning
2 DR (paid) or CR (received)
3 12-digit UPI reference (RRN), useful for disputes
4 Payee name as registered
5 Payee’s bank code
6 UPI ID (VPA)
7 Remark typed by the payer

The exact layout differs by bank (some use hyphens, some truncate names), but the method is the same.

Pull out each part

Payee:      =INDEX(TEXTSPLIT(B2, "/"), 4)
UPI ID:     =INDEX(TEXTSPLIT(B2, "/"), 6)
Reference:  =TEXTBEFORE(TEXTAFTER(B2, "/", 2), "/")
Only UPI:   =IF(LEFT(B2,3)="UPI", INDEX(TEXTSPLIT(B2,"/"),4), "")
💡 A UPI ID that is just digits before the @ (like 9876501234@ybl) is a person or small shop, not a company. Handy for spotting payments to individuals.

Common mistakes

  • The reference shown in E-notation (4.12E+11): it’s a 12-digit number. Keep it as text (the formula returns text) or format as 0.
  • Remarks containing a slash shift the parts. Count from the start for the fixed fields, never from the end.

Practice

Download the workbook below. The statement is made up but follows real Indian bank formats (NEFT, NACH, UPI narrations). The Check column turns green when your formula gives the right answer.

📎 Practice files for this article

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

Keep reading

ExcelBank Statement Lesson 7: Combine Monthly Statements With Power QueryExcelBank Statement Lesson 8: Project – Monthly Expense DashboardExcelExcel Intermediate Lesson 4: Dynamic Arrays (FILTER, SORT, UNIQUE, SEQUENCE)
Advertisement
✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong