Spreadsheets

A reusable LAMBDA to clean UPI narrations in Excel

Wrap TEXTAFTER, TEXTBEFORE, SUBSTITUTE and TRIM into one named Excel function, CleanNarr, and clean a UPI narration column. Needs Microsoft 365.

Editorial illustration for A reusable LAMBDA to clean UPI narrations in Excel

A named LAMBDA called CleanNarr can take a messy UPI narration such as UPI/DR/412345678901/BIG_BASKET /bigbasket@okaxis/Payment and return just the merchant, Big Basket. It combines TEXTAFTER, TEXTBEFORE, SUBSTITUTE, TRIM and PROPER in a single reusable function. It needs Microsoft 365, because TEXTAFTER and LAMBDA are not in older versions.

What does a UPI narration look like?

Bank statements describe UPI payments in a long string packed with reference numbers, the payee and a handle. The layout differs from bank to bank and sometimes from month to month, so open your own statement before copying anything here. For this example, assume the narration has parts separated by slashes: the word UPI, a debit marker, a reference number, the merchant, a handle and a remark.

PartExample
PrefixUPI
DirectionDR
Reference412345678901
MerchantBIG_BASKET
Handlebigbasket@okaxis
RemarkPayment

The merchant is the fourth part, so we want everything after the third slash, up to the next slash.

Step one: take the text after the third slash

TEXTAFTER(text, delimiter, instance_num) returns what follows a delimiter. For the third slash:

=TEXTAFTER(D2,"/",3,0,0,D2)

The last argument is what to return if there are fewer than three slashes. Here we return the original text, so odd rows do not turn into #N/A. The result is BIG_BASKET /bigbasket@okaxis/Payment.

Step two: cut at the next slash

TEXTBEFORE does the opposite. Taking just the merchant:

=TEXTBEFORE(TEXTAFTER(D2,"/",3,0,0,D2),"/",1,0,0,TEXTAFTER(D2,"/",3,0,0,D2))

This is already hard to read, which is the point of the next step.

Step three: tidy it and wrap it in LAMBDA

The merchant still has an underscore and trailing spaces. SUBSTITUTE swaps the underscore for a space, TRIM removes extra spaces, and PROPER sets capital letters. With LET to avoid repeating the first part, the whole function is:

=LAMBDA(narr,LET(p,TEXTAFTER(narr,"/",3,0,0,narr),m,TEXTBEFORE(p,"/",1,0,0,p),PROPER(TRIM(SUBSTITUTE(m,"_"," ")))))

Before and after: UPI/DR/412345678901/BIG_BASKET /bigbasket@okaxis/Payment becomes Big Basket. A narration with fewer parts, such as CASH WITHDRAWAL, passes through tidied: Cash Withdrawal.

Save it as CleanNarr

  1. Go to Formulas, then Name Manager, then New.
  2. Type CleanNarr as the name.
  3. In Refers to, paste the LAMBDA exactly as above, starting with an equals sign.
  4. Click OK.

Now in E2 you can type =CleanNarr(D2) and fill it down. If you prefer one formula that fills the whole column at once, use MAP: =MAP($D$2:$D$500,CleanNarr). It spills down; keep the cells below E2 empty or you will get a #SPILL! error.

What if my bank uses dashes, not slashes?

Some narrations use another separator, for example UPI-BIG BASKET-bigbasket@okaxis-Payment. The same method works: change the delimiter in both functions from the slash to a dash, and change the instance number so it counts to the part that holds the merchant. Here the merchant follows the first dash, so you would use 1 in TEXTAFTER. If a merchant name itself contains a dash, such as a hyphenated brand, the cut will land in the wrong place, so test a few rows before filling the whole column.

Narration styleDelimiterInstance for TEXTAFTERResult
UPI/DR/412345678901/BIG_BASKET /bigbasket@okaxis/Payment/3Big Basket
UPI-BIG BASKET-bigbasket@okaxis-Payment-1Big Basket
CASH WITHDRAWALnone foundfalls backCash Withdrawal

What will this not do?

  • It will not decide the category. Cleaning gives you a tidier merchant name to match against a rule or a lookup table.
  • It will not fix names that are cryptic to begin with, such as a payment to a person.
  • It will not work in Excel 2021 or older, where it shows #NAME?.

A sensible next step

Once the merchant column is clean, a lookup against a short table of merchant and category fills in categories in one pass, and a pivot table gives totals. If you repeat this every month for several accounts and the narration styles keep changing, a tool that imports statements and learns from your corrections, such as SpendixAI, may save the maintenance. The formula approach is a perfectly good way to learn how your own data behaves.

SPENDIXAI JOURNAL

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

Explore your reports