Invalid phone numbers in your CRM: how to find and fix

If thousands of phone numbers in your system are wrong, the usual cause is a validation rule that checks only one end, such as a minimum length, combined with a bulk import that bypasses the form altogether. The first thing to do is count: ask for the numbers that are too long, too short or hold anything but digits, and see which source they came from.

Here is how that looked at one business, and what cleaning it took.

An owner mentioned, almost in passing, that the phone numbers in their database looked wrong — some seemed too short, some too long. They wanted to know whether it mattered.

It is the sort of remark that usually turns out to be a handful of rows. We checked it against production, because a suspicion is not a finding until somebody counts.

What the count said

Of 753,994 live contact records, 9,300 held an unusable phone number — 1.22 per cent. 9,001 of them contained characters that were not digits at all.

That is nine thousand people the business believed it could reach and could not. Not a data-quality abstraction: a field rep pulling up a contact, dialling, and getting nothing, having travelled to do it.

Worse, it was still happening. One import route was producing bad rows at 3.48 per cent against roughly 0.15 per cent for every other source — about twenty-three times the failure rate — and was still running at 1.85 per cent this year. The pile was growing.

Nothing was checking, at any layer

The reason was not a subtle bug. It was that four separate places could have validated the number and none of them did.

The spreadsheet import copied the cell straight through. The data model declared the column as plain text. The table had no constraint. And the application’s own check — the one everyone assumed was doing the job — read:

if (contact.length < 10)

A lower bound only. Which is precisely why the database contained 7,995 eleven-digit numbers and 965 twelve-digit ones. Every one of them was tested, passed, and saved. The check was not missing. It was half-written, and half a check reads as a whole one in a code review.

The junk even named its own cause. 493 rows carried the apostrophe Excel adds when a user forces a cell to text. Ten rows held 9.88E+09 — a phone number that Excel had helpfully converted to scientific notation, destroying the digits permanently before the file was ever uploaded.

What we changed

One normaliser, deliberately written twice — once in the server, once in the mobile app, because those two cannot share code — with a parity test that runs 6,015 real production values through both and asserts they agree on every one. The asymmetry matters: if the app accepts what the server rejects, a rep is stranded in a field with a contact they cannot save. If the server accepts what the app rejects, good data is thrown away.

The import was rebuilt to behave like a tool rather than a trapdoor. It reads every row instead of stopping at the first bad one. It no longer swallows failures into a log nobody reads. It matches columns by name rather than position, so a reordered spreadsheet cannot silently load phone numbers into the village column.

And rejected rows now come back as a spreadsheet with the original columns plus an Error column explaining each one. The importer ignores that column, so the uploader fixes the numbers in place and re-uploads the same file — no reconciling two versions by hand. The phone column is written as text on the way out, because writing it as a number is how ten digits became 9.88E+09 in the first place.

What it recovered, and what it did not

After cleaning, 99.76 per cent of the records are valid. The remaining 1,844 genuinely have to be collected again — the information is gone, not malformed.

The detail worth sitting with: 1,410 of those are ten digits long with a leading digit no Indian mobile number can start with. Correct length, real digits, impossible number. The length check accepted every one of them, every time, for years.

How to find and fix wrong phone numbers in your own system

1. Count them. Ask for the numbers with the wrong length, with characters that are not digits, or with a leading digit that cannot start a valid number in your country, and compare against the total.
2. Split the count by source. One bad import route can fail many times more often than every other source, as it did here.
3. Check every door into the data: the form, the spreadsheet import, the mobile app and any integration. A rule on one of them protects only that one.
4. Make the database refuse what is impossible, so a bulk upload cannot route around the rule.
5. Let people correct rejected rows and re-upload the same file, and keep a list of the numbers that must be collected again.

Why a half-written check is worse than none

“Required” is not “valid”, and a validation rule that tests one end of a range is usually worse than none, because it creates the confidence that stops anyone looking again.

If a field matters commercially — you dial it, post to it, invoice against it — the rule belongs in the database as a constraint, not in a function somebody can route around with a bulk upload. Every one of these 9,300 rows entered through a door the application never guarded.

And when an owner says the data looks wrong, count it that week. This one was one per cent, which is small enough to argue about and large enough to have wasted a great many journeys.

Counting what an old database actually holds, and then moving the rule into the schema where a bulk import cannot route around it, is ordinary enterprise modernisation work — and it is usually the cheapest part of it.