Description
Normalizes a phone number string to E.164 format (a leading+, country code, and subscriber number with no spaces or punctuation). Use it to standardize phone values from mixed sources before storing, deduplicating, or joining on phone.
Inputs without a + country-code prefix are interpreted in the region given by the optional region argument (default US). Extensions such as x123 or ext. 5 are parsed and dropped, since E.164 does not include them.
Syntax
NormalizePhone(string_expression[, region[, on_invalid]])
Arguments
on_invalid modes
The
on_invalid fallback only triggers when normalization fails. A value that is a valid phone number always returns validated E.164 regardless of this setting.
Examples
NormalizePhone('+1 (415) 555-2671') returns '+14155552671'.
NormalizePhone('415-555-2671') returns '+14155552671'.
NormalizePhone('020 7946 0958', 'GB') returns '+442079460958'.
NormalizePhone('not a number', 'US', 'passthrough') returns 'not a number'.
NormalizePhone('32145678901234', 'US', 'e164_digits') returns '+132145678901234'.
NormalizePhone('123', 'US', 'e164_digits') returns null (fewer than 10 digits).
Return value datatype
StringImpact of null value
Returnsnull for any of the following:
- Null, empty, or whitespace-only input
- A
regionargument that is not a supported ISO 3166 region - Input that cannot be normalized when
on_invalidisnull(or omitted, or an unrecognized value) on_invalidise164_digitsbut the input has fewer than 10 digits