Spreadsheets

Remove a fixed prefix like UPI/ from narrations with SUBSTITUTE

Remove a fixed prefix like UPI/ from narrations with SUBSTITUTE in Excel, including a nested example for five prefixes and why REPLACE by position is brittle.

Editorial illustration for Remove a fixed prefix like UPI/ from narrations with SUBSTITUTE

To remove a fixed prefix such as UPI/ from a narration, use =SUBSTITUTE(A2,"UPI/","") and wrap it in TRIM if spaces are left over. To remove several different prefixes, nest one SUBSTITUTE inside another. Take care that the text you remove does not also appear in the middle of a name, and remember that SUBSTITUTE is case-sensitive.

What is the basic formula?

SUBSTITUTE takes the text, the old text, the new text and an optional instance number:

  • =SUBSTITUTE(A2,"UPI/","") removes every UPI/ in A2.
  • =SUBSTITUTE(A2,"UPI/","",1) removes only the first one, which is what you usually want for a prefix.
  • =TRIM(SUBSTITUTE(A2,"UPI/","",1)) also removes extra spaces left behind.

Using an invented narration, UPI/412345678901/SHARMA TEA STALL becomes 412345678901/SHARMA TEA STALL, so you have removed the prefix without touching the rest. The version without the 1 would also strip a UPI/ inside a payee text, so use the instance number when only a leading prefix is meant.

How do you remove five prefixes in one go?

Nest the SUBSTITUTE calls so that each one works on the result of the one inside it. The invented prefixes here are UPI/, NEFT-, IMPS/, POS and ACH:

  • =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"UPI/",""),"NEFT-",""),"IMPS/",""),"POS ",""),"ACH ",""))

Excel works from the innermost call outwards. UPI/ goes first, and ACH last. Each narration only contains one of them, so four of the five calls find nothing and pass the text on unchanged. Count the brackets: the five SUBSTITUTE calls and the TRIM make six opening brackets, and each needs its own closing one. When the formula grows beyond five, the nesting becomes hard to read. In Excel 365, list the prefixes in cells and use REDUCE instead: =TRIM(REDUCE(A2,$F$2:$F$10,LAMBDA(acc,x,SUBSTITUTE(acc,x,"",1)))).

Prefixes often come with a separator, and the separator must be part of the text you remove. Removing UPI without the slash leaves a leading slash in every row, and a clean-looking column with a stray character at the start will break a later lookup. Also watch for a non-breaking space, which TRIM does not remove. Replace it first with SUBSTITUTE(A2,CHAR(160)," "), then clean as above.

What about only removing it at the start?

If the prefix must be removed only when the text begins with it, check first:

  • =IF(LEFT(A2,4)="UPI/",MID(A2,5,LEN(A2)),A2)

LEFT tests the first four characters, and MID returns everything from the fifth. It is the most precise version, but it needs the exact length of the prefix. Count the characters, including the slash.

Why is REPLACE by position brittle?

REPLACE(A2,1,4,"") deletes four characters from the start whatever they are. If one row begins with NEFT- (five characters) and another with UPI/ (four), the formula cuts the wrong amount in one of them. SUBSTITUTE removes by content, so it adapts, while REPLACE assumes a fixed shape.

MethodRemoves byWeakness
SUBSTITUTEMatching textAlso hits the same text in the middle, unless you use the instance number
REPLACEPosition and lengthBreaks the moment a prefix has a different length
LEFT test with MIDMatch, then positionNeeds the exact prefix length

What about Find and Replace and wildcards?

Press Ctrl+H, type the prefix in Find what, leave Replace with empty and click Replace All. This is a one-time edit of the cells themselves, so work on a copy. Wildcards are supported: a question mark matches one character and an asterisk matches any run of characters. To find an actual asterisk or question mark, put a tilde in front of it. Wildcards can remove more than you expect, so try them on a few rows first. Google Sheets users can also use =REGEXREPLACE(A2,"^(UPI|NEFT|IMPS)[/-]","") to remove whichever prefix starts the text.

A short checklist

  1. List every prefix on your statement, including the separator character.
  2. Use instance 1 so that a prefix is only removed once.
  3. Check for rows that are now empty or unchanged.
  4. Compare totals before and after. They should be equal.

Removing prefixes is the first step of cleaning. If you would rather skip the formulas, SpendixAI takes statements as PDF, CSV or Excel and lets you review the rows before saving.

SPENDIXAI JOURNAL

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

Explore your reports