Brian Dunning's FileMaker Custom Functions

PhoneFormat ( Phone ; Format )

Dynamically formats phone numbers

  Average rating: 3.7 (55 votes) Log in to vote

John Mark Osborne   John Mark Osborne
Database Pros
http://www.databasepros.com/

Share on Facebook Share on Twitter

  Sample input:
PhoneFormat("909-393-4664"; "(###) ###-####")
  Sample output:
(909) 393-4664

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

A recursive phone format formula can adapt to any length phone number and any format style. Call the custom function from an auto-enter calculation to format your phone numbers when exiting the phone field. Make sure to uncheck the option to "do not replace existing value (if any)".

 

Comments

Paul N. Lawrence   Paul N. Lawrence, Texas
Dec 1, 2013
I had the problem that fields that had been edited would get the formatting characters added by default even though the fields appeared "empty" on screen.

I added an "IsEmpty" test for this great function. Copied below:

/*

PhoneFormat(Phone; Format) by John Mark Osborne, Database Pros http://www.databasepros.com/

Dynamically formats phone numbers

A recursive phone format formula can adapt to any length phone number and any format style. Call the custom function from an auto-enter calculation to format your phone numbers when exiting the phone field. Make sure to uncheck the option to "do not replace existing value (if any)".

Modified by Paul N. Lawrence December 1, 2013 to detect the empty field condition so formatting text characters are not entered automatically.

*/

Case( // added by Paul N. Lawrence December 1, 2013

not IsEmpty(Phone); // added by Paul N. Lawrence December 1, 2013

Let(

[NumbersOnly = Filter(Phone; "0123456789");
NewNumber = Right(NumbersOnly; Length(NumbersOnly) - 1);
NewFormat = Right(Format; Length(Format) - 1)];

Case(

not IsEmpty(Format);

Case(
Left(Format; 1) = "#";
Left(NumbersOnly; 1) & PhoneFormat(NewNumber; NewFormat);
Left(Format; 1) & PhoneFormat(NumbersOnly; NewFormat)
)

)

)

) // added by Paul N. Lawrence December 1, 2013
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.