The reference number in a UPI narration is usually one slash-separated segment, so you can pull it out with FIND and MID, or with Flash Fill if the layout is consistent. The part people get wrong is storing it: keep the result as text, because Excel treats long digit strings as numbers and can round them or switch to scientific notation.
Where is the reference number in your narration?
Use your own statement to find out, because layouts differ. This invented example has the reference as the second segment: UPI/412345678901/SHARMA TEA STALL/sharmatea@okbank. Many UPI references are 12 digits long, but treat that as something to verify on your file rather than a rule. Select ten narrations, mark the reference in each and check that the length is the same.
How do you extract it with MID and FIND?
With the narration in A2, the reference starts just after the first slash and ends just before the second. Two helper columns make the formula readable:
| Column | Formula in row 2 | Result for the sample |
|---|---|---|
| B (first slash) | =FIND("/",A2) | 4 |
| C (second slash) | =FIND("/",A2,B2+1) | 17 |
| D (reference) | =MID(A2,B2+1,C2-B2-1) | 412345678901 |
The length argument is 17 - 4 - 1 = 12, which matches the digits in the sample. A one-cell version looks like this: =MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1). It is harder to debug, so build the helper columns first and collapse them only when everything works.
Because MID returns text, the leading digits survive. If a reference begins with zeros, they stay.
Does Flash Fill work for this?
Often, yes. Type the first reference by hand in the cell beside the first narration, start typing the second one, and press Ctrl+E. Excel fills the rest by pattern. Format the destination column as Text before you start, otherwise the typed digits become a number. Flash Fill gives you static values, not formulas, so it will not update when you add rows. It can also guess wrongly when two narration layouts are mixed in one column, so check a sample at the end.
Why do the digits change or disappear?
Excel stores numbers with about 15 significant digits of precision. A 12-digit reference fits, but a longer one does not: anything past the fifteenth digit is replaced with zeros, and the cell may show 4.12346E+11 instead. Two habits prevent this:
- Format the destination column as Text before pasting or filling.
- When you copy formula results to values, paste into that Text-formatted column.
- If the cell already shows scientific notation, retyping will not recover digits that were lost. Re-extract from the narration.
How do you match references against another list?
The reason to extract references is matching: the same transaction in your sheet and in another export should share one. Put the other list's references in column H and test each of yours:
- =COUNTIF($H$2:$H$500,D2) returns how many times the reference appears in the other list. Be careful: COUNTIF compares number-like text as numbers, so values longer than 15 digits can match falsely.
- =SUMPRODUCT(--($H$2:$H$500=D2)) compares as text and is safer for long references.
- =ISNUMBER(MATCH(D2,$H$2:$H$500,0)) gives TRUE or FALSE for a simple present-or-missing check.
A short checklist
- Copy ten narrations and mark where the reference sits.
- Format the destination column as Text.
- Build the FIND and MID columns, or use Flash Fill and check it.
- Confirm every length is the same with LEN.
- Match against the other list with SUMPRODUCT or MATCH.
If you upload the same statement twice by mistake, SpendixAI flags possible duplicate imports and has a Possible duplicate charges report for the same amount to the same merchant within two days. That is a separate check from a reference-number match, but the two together catch most doubles.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


