Spreadsheets

Named functions in Google Sheets: reuse a formula by name

Create a named function in Google Sheets that cleans a merchant name from a bank narration, then reuse it across the sheet like any built-in function.

Editorial illustration for Named functions in Google Sheets: reuse a formula by name

In Google Sheets, a named function is a formula you save under your own name, so you can type =CLEAN_MERCHANT(C2) instead of repeating a long REGEXREPLACE every time. Open Data, then Named functions, then Add new function, give it a name and an argument, and paste the formula. It lives inside that one spreadsheet.

Which formula am I turning into a function?

Bank narrations often look like UPI-SWIGGY-9876543210@ybl. The part you care about is SWIGGY. Here is a formula for that layout, with the narration in C2:

  • =PROPER(TRIM(REGEXREPLACE(REGEXREPLACE(C2,"^(UPI|POS|IMPS|NEFT)[-/ ]*",""),"[@0-9/-].*$","")))

Reading from the inside: the first REGEXREPLACE removes a leading UPI, POS, IMPS or NEFT with its dash or slash. The second cuts everything from the first digit, @, slash or dash onward. TRIM clears spare spaces and PROPER makes it Swiggy. Test it on a few rows of your own statement first, because layouts vary by bank and you should look at yours, not assume.

How do I save it as a named function?

  1. Choose Data, then Named functions, then Add new function.
  2. Function name: CLEAN_MERCHANT. Names use capital letters, numbers and underscores.
  3. Description: Cleans a UPI narration to a short merchant name.
  4. Argument placeholders: add narration.
  5. Formula definition: the formula above with C2 replaced by narration.
  6. Click Next, add an example such as =CLEAN_MERCHANT("UPI-SWIGGY-98765@ybl"), then Create.
FieldWhat to enterWhy it matters
Function nameCLEAN_MERCHANTThis is what you type in a cell
DescriptionShort plain sentenceShows in the formula hint
ArgumentnarrationA placeholder, not a cell address
DefinitionThe formula using narrationRuns on whatever you pass in
ExampleOne sample callAppears under the hint to remind you

How do I use it across the whole sheet?

In D2 type =CLEAN_MERCHANT(C2) and drag down. To fill the column in one formula, use =ARRAYFORMULA(CLEAN_MERCHANT(C2:C500)) in D2. Say C holds three narrations: UPI-SWIGGY-98765@ybl, UPI-DMART AVENUE-12345@okbank and POS-AMAZON PAY. The results would be Swiggy, Dmart Avenue and Amazon Pay, so you can group and total by merchant without hand-editing text.

If you change the definition later, every cell using the name updates at once. That is the main reason to prefer it over copying a long formula into hundreds of cells.

Is this a custom function or LAMBDA?

Named functions are Google's no-script alternative to writing custom functions in Apps Script. You can write a LAMBDA directly inside a normal formula, but a named function lets you give that logic a reusable name and a description. The definition can include other functions, and even other named functions in the same sheet. Use Apps Script only when you need something formulas cannot do, such as fetching data from elsewhere.

A good habit is to build the function in a scratch cell first. Get one narration cleaned correctly, then two, then ten from different months, and only then save the definition. Edits to a named function change every cell that calls it, so a small mistake spreads quickly; keep a copy of the working formula in a note cell for rollback.

What could go wrong?

  • The name is rejected: it must start with a letter and contain no spaces or lowercase letters.
  • You see #NAME?: you may be in a different spreadsheet, since named functions do not travel with a copy-paste of cells.
  • The result is blank: the pattern removed the whole text, as when a narration starts with digits. Adjust the second pattern or test with a narration sample first.
  • Speed drops: ARRAYFORMULA over very large ranges is slower, so limit it to the rows you have.

When is it worth moving on?

Once you maintain several cleaning rules and a lookup list, you are building a categoriser. SpendixAI does this for imported statements, applying your own rules first and your past corrections next, and letting you add merchant aliases when a name still looks odd.

SPENDIXAI JOURNAL

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

Explore your reports