Phone number formats

• A free text field accepts any punctuation, and people write numbers the way they say them.
• Local numbers were recorded without an area code or country code because everyone nearby shared it.
• Extensions and notes such as a preferred time to call were typed into the same cell.

• Two records for the same person do not match on phone, so duplicates survive.
• Automatic dialing and text messaging reject numbers that are not in the form they expect.
• A number stored as a numeric type loses its plus sign and any leading zero.

• Remove everything except digits and count the lengths. Several lengths in one column means missing or extra prefixes.
• Search for letters. They usually mark an extension or a note.
• Look for values that begin with a plus sign beside values that do not.

• Store phone numbers as text, never as numbers.
• Move extensions to their own column before touching the digits.
• Convert to one international form, such as E.164: a plus sign, the country code, then the number without the national trunk prefix.
• Add a country code only when the country is known from other evidence. A number with no country and no area code needs a person.

• Near-duplicate recordsTwo rows describe the same real thing but differ enough that an exact comparison misses them.
• Identifiers that lost their leading zerosA code that starts with zero, such as a postal code or account number, was read as a number and the zeros were dropped.
• Multiple values in one cellA single cell holds a list, or two different facts, joined by a comma, slash, or line break.

TermsStandardization, Canonical value, String, Validation