INV-0012 or INV12? Matching references that are written two ways in Excel™
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
- Differences in case and spaces.
- Punctuation: hyphens, slashes, full stops.
- Leading zeros present in one file and not the other.
- A reference stored as text in one file and as a number in the other, so "1234" will not match 1234.
- A reference longer than 15 digits that Excel™ has already truncated or turned into scientific notation. Once that has happened in the source, matching cannot recover it. Store the column as text in the source file.
Normalise by hand
These steps apply to Excel™ in Microsoft 365. Use a helper column on each sheet.
- Strip case, spaces and hyphens:
=SUBSTITUTE(SUBSTITUTE(UPPER(TRIM(A2)),"-","")," ",""). - Handle numbers stored as text by making both sides the same type, for example
=TEXT(A2,"0")on the numeric side. - For leading zeros, either pad both sides to the same width with
TEXTor strip them on both. - Match on the helper columns, not the originals.
- 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.