Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


Format Phone Numbers with Leading Zeros via Scribe Insight in CRM

Post Author: Joe D365 |

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))))

That's it!

For additional tips and tricks, please keep following our blog. And as always, if you need additional assistance with Dynamics CRM, feel free to reach out to us.

Happy CRM'ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

3 comments on “Format Phone Numbers with Leading Zeros via Scribe Insight in CRM”

  1. I get an error when I paste this on the standard telephone1 CRM 2013 field. Does the CRM field have to be a certain type or length?

      1. Joe, I added your formula in Scribe to the telephone1 field. I wasn't doing Jscript. Since I received a SCRIBE error on the formula I was asking if the Formula needed to be used against a text type field or can it be used against the new Phone type field? I think there is an error in your syntax as I'd assume the field type (text or phone) probably doesn't matter.

PowerObjects Recommends