Skip to main content
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.

​
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

=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 short): "&raw,
  IF(AND(is_domestic, LEN(nsn)>nsn_len),"REVIEW (number too long): "&raw,
  intl))))
)

​
What the three config variables mean

VariableWhat to change
cellThe cell containing the raw phone number — change to match your column (e.g. G2)
country_codeThe default country code for domestic numbers (e.g. "44" for UK, "1" for US)
nsn_lenThe 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:
OutputMeaning
REVIEW (not a number): hello worldNo digits found at all
REVIEW (number too short): 07700Not enough digits for a valid number
REVIEW (number too long): 077009001234Too 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

CountryCountry CodeNSN Length (mobile)Notes
United Kingdom4410
United States / Canada110All NANP countries share cc=1
France339
Germany4911Mobile only; landlines vary
Spain349
Italy3910Mobiles keep leading 3
Portugal3519
Netherlands319
Ireland3539
Greece3010
Poland489
Switzerland419
Turkey9010
India9110
Australia619
Japan8110
China8611
UAE9719
Saudi Arabia9669
South Africa279
Brazil5511Mobile with 9th digit
Singapore658
Hong Kong8528
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

HubSpot setup

Connect your HubSpot portal and pick which properties Flowella reads.

Workflow actions

Pick the right phone property when configuring Send WhatsApp Template.

HubSpot sync failures

Phone-format issues are the most common cause of failed sends.

Messages not delivered

Diagnose individual sends that don’t reach the recipient.