Spreadsheets

Flash Fill to extract the payee from messy narrations

Use Flash Fill and Ctrl+E to extract the payee from messy bank narrations, with checks for wrong guesses and a note on why it does not update.

Editorial illustration for Flash Fill to extract the payee from messy narrations

Flash Fill extracts a payee by pattern: type the name you want next to the first narration, type a second example, then press Ctrl+E and Excel fills the rest. It is the quickest way to clean a column once, but it guesses, and it does not update when your data changes. Treat the result as a draft to verify.

How do you use it step by step?

  1. Put your narrations in column A and leave column B empty, immediately to the right with no blank column between.
  2. In B2, type the payee exactly as you want it for the first row, for example SHARMA TEA STALL.
  3. In B3, start typing the payee for the second row. Excel shows a grey preview of the whole column. Press Enter to accept it.
  4. If no preview appears, click the next cell and press Ctrl+E, or choose Flash Fill on the Data tab.
  5. Scroll through the filled column and compare against column A before you rely on it.

Two examples work better than one. A single example often leaves Excel unsure what you mean, so it may copy the same text down, or pick the wrong piece.

When does Flash Fill silently guess wrong?

Flash Fill learns a pattern from your examples, and it does not tell you when the pattern is only a good guess. These are the usual causes:

  • Mixed layouts. If some narrations look like UPI/ref/name/handle and others like POS name city, one pattern cannot cover both.
  • Names with different word counts. An example with two words can push it to take only two words everywhere.
  • Blank or short cells in the middle of the range, which can stop the fill early.
  • Extra spaces or odd characters that look identical but differ.

The risk is that a wrong fill looks tidy. A name taken from the wrong segment is still plausible text.

How do you check the result?

Use a formula, not your eyes alone. If the extracted payee is in B and the narration in A, this tells you whether the payee text really occurs in the narration:

  • =ISNUMBER(SEARCH(B2,A2)) returns TRUE when it does.
  • =COUNTIF($C$2:$C$500,FALSE) counts the failures, if column C holds that formula.
  • Sort column B A to Z and scan the top and bottom, where blanks and oddities collect.

Failures here are the rows to fix by hand. A payee can pass this check and still be the wrong segment, so also read a sample of rows against the original.

Why does it not update when the data changes?

Flash Fill writes plain values into the cells. There is no formula behind them, so if you edit a narration or paste new rows below, the extracted column does not change. For each new batch you repeat the examples. If you want something that recalculates, use FIND and MID, TEXTBEFORE and TEXTAFTER in Excel 365, or Power Query.

MethodUpdates on new dataBest for
Flash FillNoA one-time tidy of a single month
FIND and MID formulasYes, if copied downA fixed, known layout
Power QueryYes, on refreshA repeating monthly clean-up

Why is it not working?

  • Flash Fill needs the output column directly next to the source column.
  • It can be switched off. In Excel's options, under Advanced, look for the automatic Flash Fill setting.
  • It needs a visible pattern. If your two examples do not share one, add a third.
  • Very long or very irregular text may give no suggestion at all.

A short checklist

  1. Give two clear examples.
  2. Run the SEARCH check and fix the failures.
  3. Read twenty random rows against the source.
  4. Paste only the cleaned column into your main sheet, as values.
  5. Note the date, so you know which months were done this way.

If you are cleaning narrations every month, SpendixAI offers a different route: you upload the statement, review the rows, and its categorisation then learns from your corrections. Flash Fill remains useful for the quick, one-off job.

SPENDIXAI JOURNAL

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

Explore your reports