Spreadsheets

Clean UPI narrations into merchant names with formulas

Turn a UPI bank narration into a clean merchant name with MID, FIND, SUBSTITUTE or TEXTSPLIT, so your lookups and totals use one consistent key.

Editorial illustration for Clean UPI narrations into merchant names with formulas

Most UPI narrations are several fields joined by slashes, so the merchant name is one piece you can pull out by position. In Excel 365, =INDEX(TEXTSPLIT(B2,"/"),3) returns the third piece; in Google Sheets, =INDEX(SPLIT(B2,"/"),1,3) does the same. For older Excel, a SUBSTITUTE and MID formula does it without any newer function.

What does a UPI narration look like?

Layouts differ from bank to bank, so look at your own statement before copying any formula. For this walkthrough, assume a narration like UPI/123456/Swiggy/ybl, where the pieces are the payment type, a reference number, the payee name and a handle suffix. If yours puts the name in a different position, change the number in the formulas below.

PieceExampleUse it for
1UPITelling UPI rows from other rows
2123456Reference number, useful for matching against your bank
3SwiggyThe merchant name: your lookup key
4yblThe handle suffix, usually not needed

How do I split it in Excel 365 or Sheets?

These functions break text at a separator and let you choose a piece:

  • Excel 365: =INDEX(TEXTSPLIT(B2,"/"),3). TEXTSPLIT is not in Excel 2021 or older.
  • Google Sheets: =INDEX(SPLIT(B2,"/"),1,3). SPLIT spills across columns, and INDEX picks row 1, column 3.
  • Either app: =TRIM(...) around the result removes stray spaces.
  • Just to look at the pieces first: =TEXTSPLIT(B2,"/") in Excel 365, or =SPLIT(B2,"/") in Sheets, spills them across a row.

What works in older Excel?

The trick is to replace every slash with a long run of spaces, then cut a fixed-width slice out of the stretched text:

=TRIM(MID(SUBSTITUTE(B2,"/",REPT(" ",100)),(3-1)*100+1,100))

Change the 3 to pick another piece. It works because each piece now begins roughly at a multiple of 100 characters, and TRIM trims the padding off the result. It also runs in Google Sheets.

If you prefer finding the slashes yourself, the version with FIND is longer but easier to follow. Say the second slash is at position p2 and the third at p3. Then the name is MID(B2,p2+1,p3-p2-1), where:

  • p2 is FIND("/",B2,FIND("/",B2)+1)
  • p3 is FIND("/",B2,p2+1)

Putting them in helper columns, say D2 and E2, keeps the formula readable: =MID(B2,D2+1,E2-D2-1).

What about rows that are not UPI?

Card swipes, NEFT credits and cheque entries do not follow the same pattern, so the formula would return nonsense or an error. Guard it:

=IF(LEFT(B2,4)="UPI/",TRIM(MID(SUBSTITUTE(B2,"/",REPT(" ",100)),201,100)),TRIM(B2))

Rows that start with UPI/ are split; everything else passes through trimmed. Some statements show a payee handle, such as swiggy@ybl, instead of a name. =LEFT(x,FIND("@",x)-1) takes the part before the at sign.

How do I make the name consistent?

Even after splitting, the same merchant can come through as SWIGGY, Swiggy and swiggy. Wrap the result in PROPER to standardise capitals, then build a small alias table: raw name on the left, tidy name on the right. A lookup against it turns "Swiggy Limited", "Swiggy" and "SWIGGY INSTAMART" into whichever names you choose.

Cleaning checklist

  1. Read five real narrations from your own statement and write down which piece holds the name
  2. Pick the formula for your app and version, and test it on those five
  3. Add the UPI guard so other rows are not mangled
  4. Wrap the output in TRIM and PROPER
  5. Build the alias table for names that still differ
  6. Use the cleaned column as the lookup key for categories

Paste the cleaned values over the original once you are happy, so later formulas do not recalculate thousands of rows. If this becomes a monthly chore, SpendixAI shows the merchant name from each imported statement row and lets you add merchant aliases, so the same payee is recognised next time.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports