To strip long reference numbers from narrations, remove either every digit or only the long digit strings. Power Query does it with Text.Remove or Text.Select, a Microsoft 365 formula does it with TEXTJOIN, and Flash Fill does a quick, one-off version. The goal is for the same merchant to look the same on every row.
Why bother?
References change with every transaction, so one shop appears as many different texts. A pivot or a lookup then treats each as a separate merchant. With the numbers gone, an invented pair like ROYAL SWEETS 40021177 and ROYAL SWEETS 40021178 collapse into ROYAL SWEETS.
Should you remove every digit or only long ones?
| Approach | Good for | Risk |
|---|---|---|
| Remove all digits | Narrations where numbers are only references | Damages names such as shops with numbers, or amounts typed into the text |
| Remove only long digit runs | Mixed text with real numbers in names | A threshold that is too low or too high leaves some references in |
Look at your own file. If shop names with digits are rare, removing all digits is the simpler choice. Otherwise remove only digit strings of, say, six or more characters, and keep short numbers.
How do you do it in Power Query?
Load the data with From Table/Range, then use Add Column, Custom Column. For all digits:
- =Text.Remove([Narration], {"0".."9"})
- To keep only the digits instead, use Text.Select([Narration], {"0".."9"}).
For long digit strings only, split the narration into words, keep the short ones and rejoin:
- =Text.Trim(Text.Combine(List.Select(Text.Split([Narration]," "), each Text.Length(Text.Select(_, {"0".."9"})) < 6), " "))
Text.Split breaks the text at spaces. List.Select keeps a word only if it contains fewer than six digits. Text.Combine joins the survivors with a space. Note that a word like ABC1234567 also goes, since it has seven digits. Power Query is case-sensitive in its function names, so type them exactly.
How do you do it with a formula in Excel 365?
These need dynamic arrays. To remove only whole words that are pure numbers of six or more digits, after turning slashes into spaces:
- =TEXTJOIN(" ",TRUE,LET(t,TEXTSPLIT(SUBSTITUTE(A2,"/"," ")," "),IF(ISNUMBER(--t),IF(LEN(t)>=6,"",t),t)))
TEXTSPLIT cuts the text into words. The double minus tries to convert each word to a number, so ISNUMBER is TRUE for pure numbers only. Those with six or more characters become empty text, and TEXTJOIN with TRUE as its ignore-empty setting drops them. To remove every digit, use:
- =TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1)),"",MID(A2,SEQUENCE(LEN(A2)),1)))
MID with SEQUENCE reads one character at a time. A digit converts to a number and is replaced by empty text. Everything else is kept and joined back together. Space characters cannot be converted, so they stay.
What is the Flash Fill shortcut?
Type the clean version beside the first row, type the second one, and press Ctrl+E. It works well on a short list, but it is static and may guess wrongly, so read some rows afterwards. It will not update when you add rows.
One more habit helps: keep the numbers, just not in the merchant column. Before you strip them, copy the digit strings into their own column, using Text.Select in Power Query, or the same TEXTJOIN idea with the condition reversed. A reference you removed from the name is still useful for matching a transaction later, and keeping it beside the clean name costs nothing.
How do you know it worked?
- Count unique values before and after. The count should fall.
- Sort the result and look for rows that are now blank or very short.
- Check that totals have not changed.
- Look for rows where a legitimate number, such as a shop code that is part of the name, was removed.
If your statements arrive every month and the clean-up feels repetitive, SpendixAI uses a normalised merchant name when categorising, which handles part of this for you. Check how it treats your own statement before you rely on it.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


