To extract a merchant name from a UPI narration, use FIND to locate the slashes, then MID to cut out the text between the right two of them, and TRIM to tidy the spaces. Wrap the result in IFERROR so a row that does not fit the pattern shows a flag instead of an ugly error. It takes four helper columns, and you can see every step.
Check the layout on your own file first
Banks write narrations in different ways, and the same bank can change its format over time. The example here is invented for teaching: UPI/412345678901/SHARMA TEA STALL/sharmatea@okbank. In it, the first segment is the mode, the second is a reference number, the third is the payee name and the fourth is the payee's UPI handle. Copy ten real narrations from your own statement into a scratch sheet and note which slash-separated segment holds the name. If yours is the fifth segment, the method is identical and only the position changes.
How many slashes does each narration have?
Before you extract anything, count the delimiters. With the narration in A2, this formula returns the number of slashes:
- =LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))
- For the sample narration it returns 3, which means four segments.
- Fill it down and filter for any value other than 3. Those rows have a different shape and need separate handling.
Which helper columns find the name?
Put the narration in column A, starting at A2. Each helper column finds the next slash by starting the search just after the previous one. The FIND function takes the text to find, the text to search in, and an optional start position.
| Column | Formula in row 2 | Result for the sample |
|---|---|---|
| B (first slash) | =FIND("/",A2) | 4 |
| C (second slash) | =FIND("/",A2,B2+1) | 17 |
| D (third slash) | =FIND("/",A2,C2+1) | 34 |
| E (merchant) | =TRIM(MID(A2,C2+1,D2-C2-1)) | SHARMA TEA STALL |
Here is why column E works. MID takes the text, a starting position and a length. The name starts one character after the second slash, which is position 18. Its length is the third slash position minus the second slash position minus one, which is 34 - 17 - 1 = 16 characters. SHARMA TEA STALL is exactly 16 characters, spaces included.
Where does LEFT fit in?
LEFT is your safety check rather than the extractor. A formula that assumes UPI/ at the start should only run on rows that really start that way:
- =LEFT(A2,4)="UPI/" returns TRUE or FALSE, so you can see which rows belong to this layout.
- =IF(LEFT(A2,4)="UPI/", TRIM(MID(A2,C2+1,D2-C2-1)), "other layout") extracts only from matching rows and labels the rest.
Columns B to D will show #VALUE! on rows with fewer slashes. That is fine, because the next step handles it.
What should the final formula do when it fails?
Combine everything into one tidy formula in column E, with a flag you can filter on:
- =IFERROR(IF(LEFT(A2,4)="UPI/", TRIM(MID(A2,C2+1,D2-C2-1)), "other layout"), "CHECK")
Now filter column E for CHECK and other layout. Those are the rows your pattern did not understand, and they are the only ones you need to read by eye. Never hide the flag by returning an empty string, or a bad row will quietly fall out of your totals. You can also wrap the result in PROPER if you prefer SHARMA TEA STALL to appear as Sharma Tea Stall, though a consistent case matters more than which case you pick.
Why not just use the fixed position?
You could write MID(A2,18,16), and it would work for this row and fail on almost every other, because names have different lengths and the reference number may not always be the same width. Finding the slashes makes the formula follow the text instead of assuming it.
Before you rely on it
- Run the slash count and confirm every row you care about has the same number.
- Sort the extracted names A to Z and scan for obvious problems such as trailing digits or a handle in the name column.
- Check that your total for the extracted rows still matches the statement total.
- Keep the CHECK rows visible until you have handled them by hand.
If you find yourself repeating this clean-up every month, that is the point where a sheet starts costing more time than it saves. SpendixAI lets you upload a statement as PDF, CSV or Excel and review the rows before saving them, which may suit you better than maintaining formulas. It is optional, and the formulas above work fine for a one-off clean-up.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


