# Format Phone Numbers to E.164 in Google Sheets

> E.164 is the format ad platforms and APIs expect – and the one spreadsheets quietly destroy. Here's how to get +6591234567 out of whatever your column holds now.

- Source: https://www.8apps.co/blog/format-phone-numbers-e164-google-sheets
- Markdown: https://www.8apps.co/blog/format-phone-numbers-e164-google-sheets.md
- Published: 2026-09-02
- Author: 8apps Team
- Language: en

---
## 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 900461` **loses 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](/google-workspace/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.

## Sources

- [Google Ads Help – Format your customer data file](https://support.google.com/google-ads/answer/7659867) – read 2026-08-20
- [Meta – Conversions API customer information parameters](https://developers.facebook.com/docs/marketing-api/conversions-api/parameters/customer-information-parameters/) – read 2026-08-20
- [Microsoft – Keeping leading zeros and large numbers](https://support.microsoft.com/en-US/Excel/keeping-leading-zeros-and-large-numbers) – read 2026-08-20