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.
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.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date received | Payer | Reference as typed | Amount | Invoice key |
| 2 | 11/2/2026 | Harlow Joinery | inv 20417 | 1240 | |
| 3 | 11/3/2026 | Pennine Fixings | INV20422 | 385.5 | |
| 4 | 11/3/2026 | Castleford Glass | Inv 20 431 | 2116 | |
| 5 | 11/4/2026 | Moorside Builders | inv20409 | 640 | |
| 6 | 11/5/2026 | Ridgeway Lettings | INV 20438 | 975 | |
| 7 | 11/6/2026 | Kestrel Roofing | iNV 20445 | 1802.4 |
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.