Equation - Format Number - Phone with extension OR Emergency Services Number (911)

Greetings TB Community-

Below is an equation that uses several “IF” statements to set and format a phone number.

Scenario: You collect phone numbers from companies or individuals AND you also want to show United States emergency services phone number for some organizations (911).

The formula checks to see if an “special phone” (e.g., 911) is “Yes” or “No” (this would be a radio choice field), if it is “Yes” it will overwrite all other numbers.

If the “special phone” is “No” it will then pull any number from a “office phone number” AND check to see if there is a phone extension. If there is a phone extension, it will combine the office phone and the extension.


IF({Special Phone}='No',CONCAT(CONCAT(CONCAT('(',SUBSTRING({Office Phone}, 1, 3),')',CHAR(32),SUBSTRING({Office Phone}, 4, 3),'-',SUBSTRING({Office Phone}, 7, 4))),IF({Office Phone Extension}='','',CONCAT(CHAR(32),'ext.',CHAR(32),{Office Phone Extension}))),{Alt Phone})

If Emergency Services: 911
If NOT Emergency Services: (XXX) XXX-XXXX ext.XXXX

Hope this helps,

1 Like