Need a formula to update unformatted phone numbers?

Question

I have an import of phone numbers and fax numbers that get updated every night into the standard phone and fax fields. The problem is they get save without formatting (ex. 1234567899) I need to have these updated with the correct phone number format with the dashes (ex 555-555-5555). Does anyone know a formula I can use? to create a workflow rule to have these updated or a validation rule? Thanks.

Answer 1

Easy Fix : By default if the field you are trying to load the number is a phone field then the data is formatted automatically by salesforce.

As per docs : If you want to load the phone number to a phone number field and need to have your own formatting when you load the number add a + symbol if the your users/ BU allows that flexibility.

https://help.salesforce.com/apex/HTViewHelpDoc?id=basics_understanding_values_curr_date_phone.htm&language=en_US

When you enter phone numbers in various phone fields, Salesforce preserves whatever phone number format you enter.

NOTE:

However, if your Locale is set to English (United States) or English (Canada), ten-digit phone numbers and eleven-digit numbers that start with “1” are automatically formatted as (800) 555-1212 when you save the record

..

TIP

If you do not want the parentheses-space-hyphen formatting ((800) 555-1212) for a ten- or eleven-digit number, enter a “+” before the number.

For example: +49 8178 94 07-0. If your ten- and eleven-digit phone numbers have Salesforce’s automatic format, you may need to enter the parentheses when specifying filter conditions. For example: Phone starts with (415).

HARD FIX :

If the field you are trying to format is not a phone field and a text field use the formula here and try to accommodate the formula in a field update :

http://login.salesforce.com/help/doc/en/useful_advanced_formulas.htm

IF(Country_Code__c = 1, MID( Phone ,2, 3) &'-'& MID(Phone,7,3)&'-' & MID(Phone,11,4), Phone)

Answer 2

You can use the following methods:

Workflow Field Update

Create a new workflow rule that triggers when the phone field is populated initially or changes. Attach a Workflow Field Update that re-populates the phone value with dashes, etc, as appropriate.

Apex Trigger

You could write a short Apex Trigger that processes the values in the phone field(s) you want. This requires less maintenance but is harder to fix if something goes wrong.

Notes

• Validation rules can't alter data, so they won't help here.
• Workflow rules can cause triggers to execute twice, so be wary about using that technique if you have lots of code or installed packages.
• Triggers can be tricky to write and/or maintain, so prefer workflow rules if the above statement doesn't apply to your organization.

Answer 3

When loading multiple records Salesforce DOES NOT auto format phone fields. A formula is needed currently. If you manually enter individual records, they will be formatted correctly.