> ## Documentation Index
> Fetch the complete documentation index at: https://knowledge.flowella.io/llms.txt
> Use this file to discover all available pages before exploring further.
# WhatsApp Phone Number Formatting for HubSpot and Flowella
> Store WhatsApp numbers in E.164 format in HubSpot, import cleanly from CSV and Excel, and use the formula to convert messy numbers automatically.
If you're using HubSpot to store WhatsApp numbers for Flowella workflows, consistent formatting is essential. Numbers in the wrong format cause failed sends, and the "why is this number missing a digit" mystery is almost always a formatting issue. This page explains the correct format, how to verify it in HubSpot, how to import cleanly, and how to fix a spreadsheet full of messy numbers before import.
## Use E.164 format
Store all WhatsApp phone numbers in **E.164 format**:
* A plus sign `+`
* Country code (no leading zero)
* Full national number (without the leading trunk `0`)
**Examples:**
* UK landline: `+442079460958`
* UK mobile: `+447700900123`
Avoid spaces, brackets, and hyphens. HubSpot displays formatting nicely in the UI, but integrations — including Flowella — work best with plain E.164 values.
## Which HubSpot property holds the WhatsApp number?
Flowella writes WhatsApp-discovered contacts to a dedicated property — **`hs_whatsapp_phone_number`** — rather than the generic `phone` field.
| Scenario | Where Flowella writes / reads |
| ---------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Inbound message from a contact that doesn't exist in HubSpot yet | A new HubSpot contact is created with the WhatsApp number written to `hs_whatsapp_phone_number`. The generic `phone` property is left empty. |
| Inbound message from a contact that already exists | Flowella searches `hs_whatsapp_phone_number` **first**, then falls back to `phone` / `mobilephone` for legacy contacts. |
| Outbound from a HubSpot workflow | Pick `hs_whatsapp_phone_number` in the **Recipient Phone Number** dropdown of any Flowella workflow action. Falling back to `phone` works but is not recommended for new automations. |
If you have existing contacts whose WhatsApp number sits in `phone` or `mobilephone`, copy it across to `hs_whatsapp_phone_number` with a HubSpot workflow or import. Flowella's search falls back to the legacy fields, but **inbound matching is faster and more reliable when the WhatsApp number is on the dedicated property**.
## Verify a number is valid in HubSpot
When you click into a phone field on a contact record (for example, **Mobile phone number**), HubSpot shows a status under the input. You want it to show **Validated**.
If it does not show Validated:
* Check the number starts with `+` and the correct country code
* Check there is **no leading `0` after the country code** (for example, `+4407…` is wrong — it should be `+447…`)
* Use HubSpot's **Remove number formatting** option if you pasted a messy value, then re-save
A common mistake in Flowella workflows is pointing the action at the **Phone number** property instead of **Mobile phone number**. Double-check which property actually contains the WhatsApp-enabled number.
## CSV and Excel import tips
Excel will "help" by removing the `+` sign or converting long numbers into scientific notation. Prevent this before you import:
* Format the phone column as **Text** (Home → Number format → Text), *then* paste values
* Or prefix values with an apostrophe in Excel: `'+447700900123` — the apostrophe forces text handling and won't appear in the imported data
* In CSV files, wrap values in quotes: `"+447700900123"`
Don't import numbers with spaces or brackets. HubSpot can often normalise formatting, but clean E.164 values give the most consistent results.
## Excel formula: clean phone numbers to E.164
If you have a spreadsheet full of phone numbers in various formats, the formula below will clean them into E.164 format, ready for HubSpot import.
It handles all common input formats, including:
* Local numbers with a leading `0` (e.g. `07700 900123`)
* Numbers with `+` and country code (e.g. `+44 7700 900123`)
* International dialling prefix `00` (e.g. `0044 7700 900123`)
* The `+44(0)7...` format
* Hyphens, dots, brackets, and other punctuation
* Numbers that already have the country code but no `+` (e.g. `447700900123`)
This formula requires **Excel 365 or Google Sheets**. It uses `LET`, `TEXTJOIN`, and `SEQUENCE`, which are not available in older Excel versions.
### How to use the formula
1. Copy the formula below
2. Paste it into any cell in your spreadsheet
3. Change `A2` to point to your first phone number cell
4. Change `"44"` to your country code (see the reference table below)
5. Change `10` to the expected mobile number length for your country
6. Press Enter, then drag down to apply to all rows
### The formula
```excel theme={null}
=LET(
cell, A2,
country_code, "44",
nsn_len, 10,
raw, TRIM(cell&""),
clean, SUBSTITUTE(raw,"(0)",""),
digits, TEXTJOIN("",,IFERROR(MID(clean,SEQUENCE(LEN(clean)),1)*1,"")),
has_plus, ISNUMBER(FIND("+",raw)),
has_00, LEFT(digits,2)="00",
p, LEN(country_code),
starts_cc, AND(LEFT(digits,p)=country_code, LEN(digits)=p+nsn_len),
nsn, IF(has_plus, digits,
IF(has_00, MID(digits,3,999),
IF(starts_cc, MID(digits,p+1,999),
IF(LEFT(digits,1)="0", MID(digits,2,999), digits)))),
intl, IF(has_plus, "+"&digits,
IF(has_00, "+"&MID(digits,3,999),
IF(starts_cc, "+"&digits,
"+"&country_code&nsn))),
is_domestic, AND(NOT(has_plus), NOT(has_00), NOT(starts_cc)),
IF(raw="","",
IF(digits="","REVIEW (not a number): "&raw,
IF(AND(is_domestic, LEN(nsn)nsn_len),"REVIEW (number too long): "&raw,
intl))))
)
```
### What the three config variables mean
| Variable | What to change |
| -------------- | ------------------------------------------------------------------------------------------------------------------ |
| `cell` | The cell containing the raw phone number — change to match your column (e.g. `G2`) |
| `country_code` | The default country code for domestic numbers (e.g. `"44"` for UK, `"1"` for US) |
| `nsn_len` | The expected length of the national subscriber number for mobile numbers in your country (e.g. `10` for UK and US) |
### How the formula works
The formula takes four different paths depending on what it finds in the input:
1. **Has `+` in the original** — already international; strip to digits and prepend `+`
2. **Starts with `00`** — international dialling prefix; strip the `00` and prepend `+`
3. **Starts with the country code AND digit count matches** — bare international (e.g. `447700900123`); prepend `+`
4. **Everything else** — domestic number; strip leading `0` and prepend `+` plus the country code
Before any of this, the formula removes `(0)` from the raw input, so `+44(0)7700 900123` is handled cleanly.
### Invalid number output
Numbers that don't pass validation return a clear message so you can filter and fix them:
| Output | Meaning |
| ---------------------------------------- | ----------------------------------------- |
| `REVIEW (not a number): hello world` | No digits found at all |
| `REVIEW (number too short): 07700` | Not enough digits for a valid number |
| `REVIEW (number too long): 077009001234` | Too many digits, possibly a double prefix |
Length validation only applies to domestic numbers (those without `+` or `00`). Numbers that already have an international prefix are passed through as-is, since the formula cannot know the NSN length for every country.
## Country code reference
| Country | Country Code | NSN Length (mobile) | Notes |
| ---------------------- | ------------ | ------------------- | ----------------------------- |
| United Kingdom | 44 | 10 | |
| United States / Canada | 1 | 10 | All NANP countries share cc=1 |
| France | 33 | 9 | |
| Germany | 49 | 11 | Mobile only; landlines vary |
| Spain | 34 | 9 | |
| Italy | 39 | 10 | Mobiles keep leading 3 |
| Portugal | 351 | 9 | |
| Netherlands | 31 | 9 | |
| Ireland | 353 | 9 | |
| Greece | 30 | 10 | |
| Poland | 48 | 9 | |
| Switzerland | 41 | 9 | |
| Turkey | 90 | 10 | |
| India | 91 | 10 | |
| Australia | 61 | 9 | |
| Japan | 81 | 10 | |
| China | 86 | 11 | |
| UAE | 971 | 9 | |
| Saudi Arabia | 966 | 9 | |
| South Africa | 27 | 9 | |
| Brazil | 55 | 11 | Mobile with 9th digit |
| Singapore | 65 | 8 | |
| Hong Kong | 852 | 8 | |
The NSN length values above are for mobile numbers. For WhatsApp use, mobile-only lengths are correct. If you're processing a mix of mobile and landline numbers, be aware that some countries (especially Germany) have variable-length landlines.
## Quick troubleshooting checklist
If your Flowella workflow actions are failing to deliver messages, work through this checklist:
* Confirm the HubSpot property contains a value in **E.164 format** (starts with `+`)
* Confirm the field shows **Validated** status in HubSpot
* Make sure the number includes the country code and has **no leading `0`** after it
* Ensure your workflow action is pointing at the correct property — the one that actually contains the WhatsApp-enabled number, not a different phone field
## Related
Connect your HubSpot portal and pick which properties Flowella reads.
Pick the right phone property when configuring Send WhatsApp Template.
Phone-format issues are the most common cause of failed sends.
Diagnose individual sends that don't reach the recipient.