If you need to migrate data to Microsoft Dynamics CRM where the phone number field(s) contain leading zeros, this blog is for you! Using a migration tool such as Scribe Insight in CRM, we can use a series of functions/formulas to address this issue.
The following function will not only handle the leading zeros, but if there are exactly 10 numeric characters in the source, it will also format it as (xxx) xxx-xxxx. If there are not exactly 10 characters in the source, the function will simply populate the raw value from your source.
Here is the formula. You’ll want to replace all of the “S8″ values with the appropriate field from your source.
IF(ISERROR(S8),#NULL!,IF(S8=”NULL”,#NULL!,IF(LEN(STRIP(S8,”N”))=10,CONCATENATE(“(“,LEFT( STRIP(S8,”N”),3 ), “) “, MID( STRIP(S8,”N”),4 ,3 ),”-“,MID( STRIP(S8,”N”),7 ,4 )),LEFT(S8,30))))