Text FunctionsBeginner

Clean Up Payment References with UPPER and SUBSTITUTE

Customers type invoice numbers into their bank transfers however they like: lower case, capitals, spaces in the middle, a stray space at the end. Before those payments can be matched to the sales ledger they need one clean key, and two text functions make every version of the same reference read identically.

Task:

You do the morning bank reconciliation for a building-supplies wholesaler. This week's incoming transfers are in A2:D7, and column C holds the invoice reference exactly as each customer typed it. Your ledger stores references as capital letters with no spaces at all, like INV20417. In E2:E7, turn each typed reference into that form so it can be matched against the ledger.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

7 rows × 5 columns6 cells you fill in
ABCDE
1Date receivedPayerReference as typedAmountInvoice key
211/2/2026Harlow Joineryinv 204171240
311/3/2026Pennine FixingsINV20422385.5
411/3/2026Castleford GlassInv 20 4312116
511/4/2026Moorside Buildersinv20409640
611/5/2026Ridgeway LettingsINV 20438 975
711/6/2026Kestrel Roofing iNV 204451802.4
What this exercise teachesMay contain the answer

A computer compares text character by character, so "inv 20417" and "INV20417" are as different to a lookup as two unrelated invoices, even though a person sees the same reference at a glance. The fix is to decide what one clean form looks like and push every variant into it. SUBSTITUTE(C2," ","") replaces every space in the cell with nothing, wherever it sits: at the front, at the back, between the letters and the digits, or inside the number itself as in Castleford's "20 431". That is why it beats TRIM for this job. TRIM tidies spacing for people to read, so it keeps single spaces between words, and a key with a space in it still fails to match. UPPER then makes the letters agree, because case is the other thing people never type consistently. The order of the two does not change the answer, since removing spaces and changing case do not interfere with each other. Building the key in its own column, rather than editing column C by hand, keeps what the customer actually sent on record and means next week's statement only needs the formula filled down.