Bulk phone number formatter

One Person, Four Rows

The same customer can sit in your spreadsheet as 07700 900123, as +44 7700 900123, as 0044 7700 900123 and as 7700 900123. Four rows, four different pieces of text, one human being with one phone. Sort that column and nothing lines up. Remove duplicates in a spreadsheet and it finds none, because as text those four strings genuinely are different. The bulk phone number formatter above turns all four into the same thing and then counts three of them as duplicates.

That is most of the value in one sentence. The rest is what it does with the rows that are not any of those four shapes.

E.164, and Why Everything Wants It

There is one international standard for writing a phone number and it is deliberately boring. A plus sign, the country code, then the national number with no leading zero, no spaces and no punctuation, up to 15 digits in total. That is E.164.

WhatsApp wants numbers in that shape. So does every messaging API, every CRM import, and almost every system that will ever be handed a contact list. The reason is that it is the only format with no ambiguity in it: a number written that way means exactly one telephone anywhere in the world, and a number written any other way needs somebody to know which country it came from before it means anything at all.

Your own list is almost certainly not in that shape. Numbers arrive from booking forms, from till systems, from a member of staff typing them into a phone, and each of those has its own habits.

What the Bulk Phone Number Formatter Does to a Paste

Paste the column in, and it makes one pass over the whole thing.

It splits on line breaks, commas, semicolons and tabs, but deliberately never on a space, because 07700 900123 is one number and not two. Then it throws away everything that is not a digit or a plus sign, reading the plus first so that a number written as (+44) 7700 900123 is still recognised as international rather than losing its bracket and its meaning at the same time.

It also trims the invisible characters, which matters more than it sounds. A column copied out of a web page, a Word document or an email is full of non-breaking spaces, and a row holding nothing but one of those is a blank line rather than a broken number.

What comes out is three counters, an annotated list where every row carries a one-word verdict, and a clean list behind a copy button.

The Four Shapes It Recognises

Once a row is down to digits and a possible plus, it falls into one of four cases.

• It starts with a plus. Already international. The digits are taken as they stand.
• It starts with 00. That is the international prefix written the old way, so the two zeros come off and the rest is taken as international.
• It starts with a single 0. A national number carrying its trunk zero. The zero goes and your chosen country code goes on the front.
• It starts with anything else. A bare national number, and your country code goes on the front.

There is one clever exception in that last case. If a bare number already opens with the country code you selected, and there are still at least six digits behind it, it is treated as an international number that lost its plus somewhere in a spreadsheet rather than as a national number that happens to begin with the same digits. That is the right call almost every time, and it is the kind of thing worth knowing about before you argue with a row.

The Default Country Is Doing More Than You Think

The dropdown at the top of the panel is the most consequential control on the page, and it looks like the least.

Every row that arrives without a country code, which on a typical local list is most of them, gets that dial code welded onto the front. Choose the wrong country and the tool does not complain. It produces a list of perfectly formatted, entirely valid looking numbers that belong to other people in another country.

So set it first, before you paste anything, and set it to where your customers are rather than where you are. If your list spans several countries, the honest approach is to clean it in groups: sort by whatever tells you the country, paste one group at a time, and change the dropdown between them. One pass over a mixed list will get the majority right and quietly ruin the rest.

Rows that already carry a plus or a 00 are unaffected by the dropdown, which is why an international row survives a wrong setting and a local one does not.

Ready, Duplicate, Invalid

Three counters sit above the list, and they always add up to the number of entries read. If they do not match what you expected, the gap is the interesting part.

Every row in the list carries one of five verdicts. Ready means it is clean and it is the first time that number has appeared. Duplicate means the number is fine but you have already got it. Too short, Too long and No digits are the three ways a row fails.

No digits is usually a header row, a name that came along with the column, or a cell holding a note instead of a number. Those are harmless and they are worth glancing at, because a stray one often means the paste started a row too high.

Too Short and Too Long

A cleaned number has to land between 8 and 15 digits, country code included. Fifteen is not an arbitrary choice: it is E.164’s own ceiling, the longest a telephone number is allowed to be anywhere.

Too short is almost always a number that never had enough digits to begin with: a truncated cell, a number stored without its area code, an extension typed on its own. Occasionally it is a genuine short code, which is not a number a person can be messaged on anyway.

Too long almost always means two things ended up in one cell. A space is never a separator here, so a cell holding 07700900123 07700900456 is read as one number 22 digits long, cleans to 23, and fails. An extension typed on the end does the same: 07700 900123 ext 4501 comes out at 16 digits and fails. Both are the tool telling you something about your source data rather than about the number.

There is one shape it cannot save you from, and it is a common one. A number written as +44 (0)7700 900123, which is how a great deal of printed stationery writes a number so that it works at home and abroad, keeps that zero. The plus tells the cleaner to trust the digits, and those digits include a trunk zero that should have gone. You get 13 digits, a row marked Ready, and a number that is wrong. If your list came from anywhere that writes numbers with a bracketed zero in them, take the brackets out before you paste.

Duplicates Are Caught on the Digits

The duplicate check runs on the finished number rather than on what you pasted, which is the entire trick. The four rows in the opening paragraph collapse into one because after cleaning they are the same fourteen digits, whatever they looked like going in.

The first appearance is kept and marked Ready. Every later one is marked Duplicate and left out of the clean list, so what the copy button hands you is one row per person.

Two things it will not catch, and both are worth knowing. If a customer has given you two different numbers, a mobile and a landline, those are two numbers and it has no way to tell they are one person. And a number typed with a genuine mistake in it, one digit wrong, is a different number rather than a duplicate. Cleaning a list is not the same as reconciling one.

Nothing Leaves Your Browser

The line under the paste box says nothing leaves your browser, and on this panel that is a description of the code rather than a promise about intentions. There is no request of any kind behind this tool: nothing is uploaded, nothing is posted to a server, nothing is logged. The cleaning happens in the page in front of you.

That matters because a pasted customer list is somebody’s contact database, and handing one to a website you found this morning is a bigger decision than it feels like when you are in a hurry.

Two practical limits come out of the same design. It reads up to 5,000 entries in one go, and it draws up to 300 rows in the scrolling list. The counters and the copied list still cover everything it read, so a big paste is fully cleaned even though you are only shown the top of it, and anything beyond 5,000 is reported rather than silently dropped.

What the Bulk Phone Number Formatter Cannot Tell You

A clean number is a well formed number. That is all it is, and the distance between that and a number you can message is worth spelling out.

• It does not know whether the number exists. Nothing is looked up. A perfectly formatted number can belong to nobody at all.
• It does not know whether it is on WhatsApp. A landline cleans exactly as neatly as a mobile does.
• It cannot tell a mobile from a fixed line. That distinction lives in national numbering rules, and this is a formatter rather than a directory.
• It does not know whether the number is still that person’s. Numbers get recycled, and a list from 2019 has some in it.
• It says nothing about permission. This is the important one. A formatted list is not a consenting list, and the tidiness of the column has no bearing on whether those people agreed to hear from you.

The panel itself is built on the server before any script runs, so a list you paste is cleaned by the page and re-cleaned as you type, and neither pass involves anything outside your own browser.

What to Do With the Clean List

Copy it, then spend five minutes on the rows it rejected rather than throwing them away. The invalid count is a report on your data, and the same problem will be waiting in the next export unless somebody fixes it at the source.

Then think about who is on the list before you think about what to send it. A cleaned column of 3,000 numbers is not 3,000 people waiting to hear from you, and the fastest way to damage an account is to treat it as though it were. Check what a send to that list would actually cost with the broadcast cost calculator, and check whether your account can even reach it in a day with the WhatsApp messaging limit calculator.

The last thing worth saying is that this should be a one-off. With DMly the de-duplication happens at import, one record is kept per customer, and the channel they arrived on is remembered, so the list stays clean instead of needing this treatment again next quarter. Cleaning the same spreadsheet twice a year is a sign of where the numbers are being stored, not of how messy your customers are.