FormatPhone ( phone )

Auto formats all US based phone numbers, along with extensions.

Average rating: 4.6 (37 votes) Log in to vote

Sravan Puttagunta
Mann Consulting
http://www.mann.com

Sample input:

FormatPhone (3333333333123)
FormatPhone (1-333-333-3333x123)
FormatPhone (333-333-3333 123)
FormatPhone ((333)333-3333 123)
FormatPhone (333-333-3333x123)

Sample output:

1(333) 333-3333 x123

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

Let(

@NumbersOnly = TextFormatRemove(Filter(phone; "0123456789"));



Case(


Left(@NumbersOnly;1) = 0;

   phone;

Left(@NumbersOnly;1) = 1 and Length(@NumbersOnly) = 11;

  "(" & Middle(@NumbersOnly; 2;3) & ") " & Middle(@NumbersOnly; 5;3) & "-" & Right(@NumbersOnly;4);

Length(@NumbersOnly) = 10;

  "(" & Left(@NumbersOnly; 3) & ") " & Middle(@NumbersOnly; 4; 3) & "-" & Right(@NumbersOnly; 4);

Length(@NumbersOnly) = 7;

  "(415) " & Left(@NumbersOnly; 3) & "-" & Right(@NumbersOnly; 4);

Length(@NumbersOnly) > 10;

  "(" & Left(@NumbersOnly; 3) & ") " & Middle(@NumbersOnly; 4; 3) & "-" & Middle(@NumbersOnly; 7; 4) & " x" & Right(@NumbersOnly; Length(@NumbersOnly) - 10);

phone

)

)

Useful for entering phone numbers

Comments

		Jens Rasmussen, KÃ¸benhavn K Jul 3, 2009
I can't see what this does that Proper doesn't do.

		Vince Dolan, St Paul Jul 3, 2009
It appears to give you the option to call out the number of "capped" words, where as Proper "caps" them all. Not sure of its actually utility though.

		Matt, Northern Vermont Jul 9, 2009
Just what we need. Another phone number formatter. What does this make? Eight for North American numbers?

		Will Loving, Dedication Technologies, Inc. Aug 23, 2010
No need to be snide Matt. Everyone is entitled to their take and to try their hand at things.

		Luis Gonzalez/AFS Group,Inc, Miami, Fl Dec 21, 2010
I am sorry that I havenâ€™t seen these comments before, but what we need is constructive ones. So, every one just pls. limit it to make all the community to grow.

		Paul, Peak 14 LLC Aug 20, 2013
Neat bit there where a missing area code (often left out in legacy data and in fast data entry because it is always the same) gets interpolated into the phone number. Slick and just what I needed! thanks.

		Adam, Los Angeles, CA Feb 5, 2015
I appreciate it very much and it works great!!! Matt I wish I was as smart as you, but I am not and so I appreciate help when I can get it. Please do not hack in my computer for saying that! Thank You

		Sep 23, 2019
didn't help me; I wound up breaking the phone number into text field with embedded 3 parts

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

FormatPhone ( phone )

Comments

Support this website.

Support this website.