Best tool for formatting phone numbers
Formatting phone numbers means rewriting a column of inconsistently typed numbers into one machine-readable shape — almost always E.164, the ITU-T international numbering plan that CRMs and SMS platforms expect. An E.164 number starts with a plus sign, carries a country code of one to three digits, never begins with a zero, and runs to at most 15 digits total. The hard part is not the format itself but the input: a real contact export mixes local formats, bracketed area codes, spaces, dots, extensions, and numbers whose country you have to infer. The right tool depends on whether you need a one-off column rewritten or per-number validation against live carrier data.
What to look for
- E.164 output: produces the plus sign, country code, and digits-only national number that importers actually validate against
- Country inference: works out the country code for numbers typed in local format, rather than blindly prefixing one
- Leading-zero safety: does not silently destroy numbers stored as text before you ever get to format them
- Bulk handling: rewrites a whole column in one pass instead of one number at a time
- Privacy: whether the numbers are processed on your device or transmitted to a third-party server — phone numbers are personal data under GDPR
- Effort: how much code, setup, or account creation stands between a raw export and a clean column
The tools, compared
Purpose-built for this exact task: point it at a column, pick a default country, and it rewrites the whole thing to E.164 in one pass. It runs in the browser, so a contact list full of personal data never leaves your device. It does not check whether a number is currently in service or which carrier owns it — that needs a live lookup API.
Visit siteGoogle's parsing and formatting library, and the reference implementation almost every other tool wraps. Its E164 format mode is exactly correct on per-country rules and edge cases. The catch is that it is a library, not a tool — you need to write Java, C++, or JavaScript to use it, which rules it out for anyone with a spreadsheet and a deadline.
Visit siteThe most authoritative option for validation, and basic lookup is free — it returns E.164 and national formats and reports typed errors like TOO_SHORT and INVALID_COUNTRY_CODE. But it is an API: you need an account, code to loop over the column, and every number is transmitted to Twilio. Right for verifying a list you are about to send to; heavy for reformatting one.
Visit siteProcesses data on your own machine rather than in the cloud, which makes it a genuine privacy peer, and its transform expressions can do the job. But it has no phone-specific function — you write GREL regular expressions per country and handle the edge cases yourself — and it needs a Java install first.
Visit siteFine if the data is already in Sheets: REGEXREPLACE will strip separators and a concatenation adds the country code. There is no phone-aware feature, so every country rule is one you hand-write, and the file sits on Google's servers throughout.
Visit siteActively works against you here. Microsoft documents that Excel removes leading zeros and converts large numbers to scientific notation on open, and warns that reformatting the cell afterwards will not restore zeros that were already removed — the damage happens before you start. The Special > Phone Number format is a display mask shaped around 10-digit US numbers, so it changes what you see, not what exports.
Visit siteWhy Sigmera
Most of the tools above are either a library you have to program against or a service you have to send your contacts to. Sigmera sits in the gap: a column of mixed-format numbers goes in, E.164 comes out, and the file is parsed by your own browser rather than uploaded to a server. That matters more for phone numbers than for most columns, because they are personal data — GDPR's data-minimisation principle asks you to limit processing to what is necessary, and a formatting pass that transmits every subscriber's number to a third party is hard to justify when the same work can happen locally. It also sidesteps the Excel trap entirely: because the CSV is read as text from the start, leading zeros and long international numbers survive instead of being rounded or rewritten in scientific notation before you notice.
Sources
- 1.ITU-T Recommendation E.164: The international public telecommunication numbering plan
- 2.What is E.164? — Twilio Docs
- 3.Keeping leading zeros and large numbers — Microsoft Support
- 4.Set up phone number property validation — HubSpot Knowledge Base
- 5.Lookup v2 API — Twilio Docs
- 6.Art. 5 GDPR — Principles relating to processing of personal data
Frequently asked questions
- What is E.164 format for phone numbers?
- E.164 is the ITU-T international public telecommunication numbering plan. A number in E.164 starts with a plus sign, followed by a country code of one to three digits and the national number, with no spaces, brackets, or dashes. The total is capped at 15 digits, and it never starts with a zero because no country code does. A UK number written 020 7946 0958 locally becomes +442079460958.
- Why do CRMs require E.164 phone numbers?
- Because it is the only format that is unambiguous worldwide. HubSpot's phone-number validation, for example, requires a valid country code starting with a plus sign, between 4 and 15 digits, and no characters other than digits and that leading plus. Without a country code, a number is only meaningful to someone who already knows which country it belongs to — which an SMS gateway does not.
- Why does Excel ruin my phone numbers?
- Excel treats a column of digits as a number, not a label. Microsoft's own documentation states that Excel automatically removes leading zeros and converts large numbers to scientific notation so that formulas and maths work on them, and that numbers past 15 digits get rounded. Crucially, the same page warns that formatting the cell as text afterwards will not restore leading zeros that were already removed, so the fix has to happen before or instead of opening the file in Excel.
- Can I format phone numbers without uploading my contact list?
- Yes. A browser-based tool parses the file with JavaScript on your own device, so the numbers are never transmitted anywhere. OpenRefine achieves the same thing as a local desktop application. The options that do require transmitting your list are the validation APIs, such as Twilio Lookup, because checking whether a number is live inherently means asking a server.
- How do I add country codes to a column of local numbers?
- Set a default country for the column and let the tool apply that country's code to any number lacking one, then review the exceptions. This is safe when a list is single-country and risky when it is not — a number that already carries a different country code must not be prefixed again. If the list spans several countries with no country column to key on, some numbers will need manual review whatever tool you use.
- What is the difference between formatting and validating a phone number?
- Formatting rewrites the number into a consistent shape and can confirm it is structurally plausible for its country. Validating checks it against live data — whether the number is currently assigned, which carrier holds it, and whether it is a mobile or a landline. Formatting works on the file alone; validation always requires a network lookup, which is why the two capabilities live in different tools.