AppGarden Two-File Reconciliation logo AppGarden

Two-File Reconciliation

INV-0012 or INV12? Matching references that are written two ways in Excel™

By Paul Kemp · Published

One system writes INV-0012. The other writes inv 12 or INV12. They are the same invoice, but a lookup sees three different strings and reports breaks that are only formatting.

The usual suspects

Normalise by hand

These steps apply to Excel™ in Microsoft 365. Use a helper column on each sheet.

  1. Strip case, spaces and hyphens: =SUBSTITUTE(SUBSTITUTE(UPPER(TRIM(A2)),"-","")," ","").
  2. Handle numbers stored as text by making both sides the same type, for example =TEXT(A2,"0") on the numeric side.
  3. For leading zeros, either pad both sides to the same width with TEXT or strip them on both.
  4. Match on the helper columns, not the originals.
  5. Keep the original column beside it so you can see what was changed.

Be careful not to over-normalise. Removing all punctuation from references that rely on it can join two different references.

Let the matcher normalise for you

Two-File Reconciliation normalises spaces, case, punctuation and leading zeros by default, so INV-0012, inv 12 and INV12 can be treated as one invoice. It also reads a text "1234" and the number 1234 as the same. The rows it matched and how it matched them are written to the report, and the Audit sheet records every normalisation switched on. Free for up to 1,000 rows a side.

See Two-File Reconciliation for Excel™

Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Two-File Reconciliation is not affiliated with or endorsed by Microsoft.