Spreadsheets

REGEXREPLACE to strip reference numbers from narrations in Sheets

Strip long reference numbers, dates and stray symbols from bank narrations with REGEXREPLACE in Google Sheets, with three patterns and what each one removes.

Editorial illustration for REGEXREPLACE to strip reference numbers from narrations in Sheets

REGEXREPLACE swaps text that matches a pattern for something else, and replacing with nothing deletes it. Chain three patterns, one for dates, one for long digit strings and one for stray symbols, and a narration full of reference numbers shrinks down to the merchant words that stay the same each time.

Why bother cleaning narrations?

A merchant that appears with a different reference number each time looks like many merchants. Grouping or searching then fails. Strip what changes and what remains repeats, so a pivot or QUERY sees one merchant.

What are the three patterns?

PatternRemovesExample input to output
[0-9]{2}[-/][0-9]{2}[-/][0-9]{2,4}Dates like 12-05-25 or 12/05/2025ref 12-05-25 becomes ref
[0-9]{6,}Any run of six or more digits, such as reference numbers412345678901 disappears
[^A-Za-z ]+Anything that is not a letter or space: digits, slashes, at signs, dotsswiggy@okaxis becomes swiggy okaxis

The curly brackets are quantifiers: {6,} means six or more times, {2,4} means two to four times. The square brackets list allowed characters, and the caret inside them means not.

How do I chain them?

Say the narration is in B2 and reads, in a made-up example, UPI/412345678901/SWIGGY ORDER/swiggy@okaxis/12-05-25. Nest the calls so dates go first:

  • =TRIM(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(B2, "[0-9]{2}[-/][0-9]{2}[-/][0-9]{2,4}", " "), "[0-9]{6,}", " "), "[^A-Za-z ]+", " "))

The steps on that example:

  1. After the date pattern: UPI/412345678901/SWIGGY ORDER/swiggy@okaxis/ (the date is replaced by a space).
  2. After the long digits pattern: UPI/ /SWIGGY ORDER/swiggy@okaxis/ .
  3. After the symbols pattern: UPI SWIGGY ORDER swiggy okaxis with spaces in the gaps.
  4. After TRIM, which also collapses repeated spaces: UPI SWIGGY ORDER swiggy okaxis.

Dates go first for a reason: the date pattern needs the dashes still present. If you stripped symbols first, 12-05-25 would become 12 05 25 and the date pattern would no longer find it.

How do I tidy the result further?

  • Remove a fixed prefix: wrap with REGEXREPLACE(x, "^UPI ", "") to drop the leading word.
  • Make it uniform: wrap the whole thing in UPPER or PROPER.
  • Drop the UPI handle at the end: REGEXREPLACE(x, " [a-z]+$", "") removes a trailing lowercase word, but check that it does not cut real names.
  • Fill down in one go with ARRAYFORMULA around the whole formula, using B2:B500 in place of B2.

What if two spellings are still different?

Cleaning will not make Swiggy and Swiggy Instamart equal. Build a small two-column lookup of messy name and tidy name, and use VLOOKUP or a nested IF to map them. Check results against a sorted list of unique cleaned names.

How do I check it worked?

  1. Copy the cleaned column and paste values only, then sort A to Z.
  2. Scan for rows still holding long strings or stray symbols.
  3. Use the UNIQUE function on the cleaned column and count the rows. If 300 narrations reduce to 40 names, you have a workable list.
  4. Spot-check three rows against the original narration.

Should I use REGEXREPLACE or SUBSTITUTE?

SUBSTITUTE swaps one exact piece of text, which suits a prefix like UPI/ that never changes. REGEXREPLACE swaps anything that fits a pattern, which suits reference numbers that change each time. Use the simpler one when the text is fixed, and keep REGEXREPLACE for the parts that vary. Mixing them is fine: SUBSTITUTE first for the fixed words, then REGEXREPLACE for the numbers.

If you run this every month, an importer that handles statements for you saves the rebuild. SpendixAI normalises merchant names on import and lets you add merchant aliases, and you review rows before saving.

SPENDIXAI JOURNAL

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

Explore your reports