What E.164 is, in ten seconds
E.164 is the international phone format: a +, the country code, then the number, no spaces or punctuation – +6591234567, +14155552671. It is what Google Ads asks for on customer lists ("Format phone numbers using the E.164 format", including "the country code and '+' sign"), and what most APIs mean when they say "international format".
First: stop the spreadsheet destroying the data
Phone columns die before any formula runs:
- A long number becomes scientific notation the moment Sheets reads it as a number.
07700 900461loses its leading zero – and once lost, it is gone; reformatting later is data loss, not formatting.+44…can be read as a formula and error out.
The fix is one habit: format the column as plain text before data lands in it (Format → Number → Plain text). Fixing afterwards does not recover what the import already destroyed.
The cleanup formulas
Strip everything that is not a digit:
=REGEXREPLACE(TO_TEXT(A2), "\D", "")
(650) 555-1212 becomes 6505551212; +44 7700 900461 becomes 447700900461. Then the judgement call formulas cannot make for you – the country code:
- Number already carries it (11+ digits starting with your country code): prepend
+and done. - Domestic format with a leading zero (UK, much of Europe): drop the zero, prepend code –
07700 900461→+447700900461. - Bare local number (US
6505551212): prepend+1.
For a single-country list:
="+65" & REGEXREPLACE(TO_TEXT(A2), "^0*|\D", "")
For mixed-country lists there is no formula that guesses correctly – you need the country as its own column, and a lookup from country to dialling code. That is not pedantry; a wrong guess produces a valid-looking number for the wrong person.
The reason this matters more than it used to
If the list is heading to an ad platform as hashed identifiers, formatting stops being cosmetic. Hashing is exact: +6591234567 and 6591234567 produce completely different digests, and only one of them can match. Google Ads documents the E.164-with-plus form; Meta documents digits-with-country-code and its own example is 16505551212 – no plus. The same phone number needs two differently formatted inputs for the two platforms. Format first, per destination, then hash.
Where Hash Data fits
Hash Data works inside the sheet you are already in. Its =HASHPHONE() function normalises a number to E.164 and hashes it in one step – the whole pipeline above, without chaining REGEXREPLACE into a digest formula and maintaining it.
→ Hash Data on the Google Workspace Marketplace
Sources
- Google Ads Help – Format your customer data file – read 2026-08-20
- Meta – Conversions API customer information parameters – read 2026-08-20
- Microsoft – Keeping leading zeros and large numbers – read 2026-08-20