Spreadsheets

Extract the UPI handle after the @ sign from a narration

Extract the text after the @ sign in a UPI narration with TEXTAFTER or MID, group transactions by handle and use it carefully to review payees.

Editorial illustration for Extract the UPI handle after the @ sign from a narration

To extract the UPI handle, take everything after the @ sign in the payee ID. In Excel 365 that is =TEXTAFTER(A2,"@"). In any version, use =MID(A2,FIND("@",A2)+1,LEN(A2)). Group the results to see which handles recur, but treat the handle as a clue to review rather than proof of who or what the payee is.

What are the formulas?

Say A2 holds the invented narration UPI/412345678901/SHARMA TEA STALL/sharmatea@okbank. The handle is okbank, the part after the at sign.

Excel versionFormulaResult
Microsoft 365=TEXTAFTER(A2,"@")okbank
Any version=MID(A2,FIND("@",A2)+1,LEN(A2))okbank
With a fallback=IFERROR(MID(A2,FIND("@",A2)+1,LEN(A2)),"no handle")okbank, or no handle

The MID version starts one character after the at sign, and LEN gives a length that is always long enough, because MID stops at the end of the text. Narrations with no at sign, such as card or NEFT rows, return the fallback.

What if more text follows the handle?

Some narrations continue after the handle, for example with another slash and a note. Then cut at the next delimiter. In Excel 365:

  • =TEXTBEFORE(TEXTAFTER(A2,"@"),"/",1,0,1)
  • The final 1 tells TEXTBEFORE to treat the end of the text as a delimiter, so it still works when nothing follows.
  • In older versions, find the slash after the at sign and cut: =IFERROR(MID(A2,FIND("@",A2)+1,FIND("/",A2,FIND("@",A2))-FIND("@",A2)-1),MID(A2,FIND("@",A2)+1,LEN(A2)))

How do you group transactions by handle?

With the handle in column F and the debit amount in column D, you can count and total per handle. In Excel 365, first list the distinct handles in H2 with =UNIQUE(F2:F500). Then in I2 and J2:

  • =COUNTIF($F$2:$F$500,H2) gives the number of payments to that handle.
  • =SUMIFS($D$2:$D$500,$F$2:$F$500,H2) gives the total paid.

A pivot table does the same without formulas: put Handle in Rows and Amount in Values. Sort by the total, largest first, and you will see which handles carry the most money.

Can the handle tell a person from a business?

Only as a weak hint. The text before the at sign is chosen by the account holder or generated by an app, and the part after it identifies the bank or app that issued the address. Some personal IDs are built from a mobile number, and some businesses use a name, but that is a habit rather than a rule. Do not decide a payee's identity from a suffix alone, and do not map a suffix to an app or a bank unless you have checked it. A shop may use any handle, and a friend may use any other.

A safer use is to sort. First put the part before the at sign in column E, with =TEXTBEFORE(payee ID cell,"@") in Excel 365. Then add a simple tag, for example "ID starts with digits", using =IF(ISNUMBER(--LEFT(E2,10)),"digits","name"), and review the digit group by hand. It narrows what you look at without pretending to know the answer.

Handles are still useful for housekeeping. If the same payee appears under two handles, you may be looking at one shop with two payment addresses, or two different people with the same name. A name alone cannot settle that. Put the name and handle side by side in a pivot, with Name in Rows and Handle in Columns, and look at the count in each cell. A name with several handles and many payments deserves a note in your sheet, so you remember what you decided.

A short checklist

  1. Extract the handle, with a fallback for rows that have none.
  2. Check for stray spaces with TRIM and for case differences with LOWER, so okbank and OKBANK group together.
  3. Group by handle and look at the top five by total.
  4. Review unfamiliar handles yourself rather than guessing.

The extracted handle helps you audit, and the real categorisation still needs your judgement. SpendixAI keeps whatever category you correct and reuses it for similar merchants later, which can save repeating the same review each month.

SPENDIXAI JOURNAL

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

Explore your reports