Phone Number OCD Test in Salesforce?

Here’s a quick OCD test for you: does anything bug you about the Phone column in the sample data below?

Take a look at the number for Edward Stamos. No parentheses around the area code. Same for Jon Amos, and he has periods in his number.

While this may be an aesthetic irritant for some of us, this could actually hinder some apps that make use of phone numbers, such as a phone dialer or SMS texting app. So what’s going on here?

Phone number fields in Salesforce are special because they store data without formatting, but may display it in the local formatting for phone numbers. According to the documentation...

When you enter phone numbers in various phone fields, Salesforce preserves whatever phone number format you enter. However, if your Locale is set to English (United States) or English (Canada), 10-digit phone numbers and 11-digit numbers that start with “1” are formatted as (800) 555-1212 when you save the record.

That means, if you enter 1234567890 and save, the number will display as (123) 456-7890. However, if you enter 123 456-7890, or any permutation that includes non-digits such as a period, a dash, a plus sign, or a parenthesis, the automatic formatting does not work. It gets saved as a string of characters rather than a phone number. Same thing if you add x123 at the end.

If you enter (123) 456-7890, this will display exactly as if you had entered 1234567890, but it’s actually different data. The difference is that apps looking for a phone number may not recognize that first entry with the parentheses, the space, and the dash.

When I was creating Proton Text, an SMS texting app for Salesforce, I added code to clean numbers with extraneous characters. The code uses a simple REGEX filter to strip out all non-digit characters. I recognized that virtually every user will be entering phone numbers at one time or another, so it’s unreasonable to rely on good data entry habits. Better to let the code do it. Note that I don't actually change the data that is saved, but I clean it before trying to use it.

So, what happens if you have thousands of records with inconsistent phone number data? (This is especially likely if you have imported lists of Leads with phone number fields.) If it’s just a cosmetic nuisance, I’d live with it. But if you have an app that can’t handle it, you have two tasks: 1) fix old records with an Apex method that finds dirty numbers and cleans them, and 2) implement a workflow or validation rule that assures only clean data goes in for new records.