Formatting Telephone Numbers in Data Sync using Calculated Column Expressions

Inspired by the posting on excellent PowerObjects blog called Format Phone Numbers with Leading Zeros via Scribe Insight in CRM(http://www.powerobjects.com/blog/2013/07/31/format-phone-numbers-via-scribe-insight-in-crm) I wanted to demonstrate how to format a telephone number using Calculated columns.

The challenge is to format a raw value in a field called into a telephone number if it is of the correct format. JoeCRM on PowerObjects defines it more clearly:

'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.'

In Scribe Insight you would enter the following transformation:

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

Note: the S8 refers to the field index but is not the field name.

Using DS3's Calculated Column Editor you would generate the following expression where the field is called targettext. Obviously commenting your expression as not to cause immense pain when coming back to it later:

Note: You can get a full list of Functions here

Calculated Columns

Calculated columns offer the following efficiency, productivity and quality benefits:

• The comments are preserved and appear in the Automatically generated documentation

• When putting together an expressions in Calculated Columns intelli-sense makes it easier and more accurate:

• If you need to refer to a source column you have full auto-complete of columns and expressions: