I'm new to FileMaker. What is the field calculation for automatic correction of a phone number?

For example, if a user enters the phone # 0123456879. I want the field to automatically adjust the numbers with the parentheses around the area code w/ 1 space after the parentheses.

Here what I want it to look like (012) 345-6789. I want the field to automatically update entries to look like that.

Thanks!

Maulanday,

Please take a look at This thread for options.

The built-in manual/"FMP Help" states:

The following example removes all text from the provided data, then formats the remaining numbers in the preferred phone number formatting:

Let(phone = filter(theField;"0123456789");"(" & left(phone;3) &

")" & middle(phone;4;3) & "-" & middle(phone;7;4))

If theField contains Work: 408.555.1212 this calculation returns (408)555-1212.

Hope that helps.

I have extensions for some of them.

So I actually want it to look like this

(012) 345-6789 ext. 123. If there is an extension.

Are they always 3 digit extensions?

If so this should work fine.

/* Strips out any not numeric characters then;

Takes a 7 digit phone number and formats it as (918) xxx-xxxx [color:red](918) is our local area code

Takes a 10 digit phone number and formats it as (xxx) xxx-xxxx

Takes a 13 digit phone number and formats it as (xxx) xxx-xxxx ext. xxx

[color:blue]phone_number is my field name

*/

Let ( digits = Filter ( [color:blue]phone_number ; "0123456789" );

Case (

Length ( digits ) = 7;

("([color:red]918) " & Left ( digits; 3 ) & "-" & Right ( digits ; 4 ));

Length ( digits ) = 10 ;

("(" & Left ( digits; 3 ) & ") " & Middle ( digits; 4 ;3 ) & "-" & Right ( digits ; 4 ));

Length (digits) = 13

("(" & Left ( digits; 3 ) & ") " & Middle ( digits; 4 ;3 ) & "-" & Middle (digits; 7; 4) " ext. " & Right ( digits ; 3 ));

Length ( digits ) = 0 ;

("") ))

I think thats right!

[color:green]ALSO...IN CASE NO ONE HAS MENTIONED IT...

SET THE CALC UP AS AN AUTO-ENTER CALCULATED VALUE AND UNCHECK THE "DO NOT REPLACE EXISTING VALUE FOR FIELD"

Edited November 17, 200520 yr by Guest
CLARIFICATION