Here's Another CF Phone Format

[SteveB]

Here's a Custom Function that formats phone numbers. It removes a leading '1' and handles 10 and 7 digits. If the format is incorrect (not 7 or 10), it changes the text color to red as an indicator for the user. It requires 1 function parameter called 'Phone':

Let (

[ num = Filter ( Phone ;"0123456789" );

num = Case(Left(num;1)= "1"; Middle(num;2;20); num);

len = Length(num) ];

Case( len = 10; TextColor(Left( num; 3) & " " & Middle( num; 4; 3) & "-" & Middle( num ;7;4); RGB(0;0;0)); // with area code

len = 7; TextColor( Left( num; 3) & "-" & Middle( num;4;4); RGB(0;0;0)); // without area code

TextColor(num; RGB(255;0;0) ) ) // wrong length so make text red

)

See my other posting in this forum 'This is for Comment' for more flexible methods.

Steve

[Steverino]

What do you do about international numbers mixed with (mostly) domestic phone numbers?

[SteveB]

My customers don't record international phone numbers, so I never put in the capability. Do all international phone numbers follow one standard format? Does it vary by country?

Steve

[Steverino]

Usually they start with the foreign-country code (011), but they might have any number of digits after that.

[SteveB]

Then how can it be formatted? Does it always start with a '0'?

Steve

[Kent Searight]

Calling a foreign country from within the USA will always start with "011" (except country codes 808 and 809, Dominican Republic and Midway, respectively) but the country's code, which follows 011, can be from 1 to 5 digits long. I believe you'd need a database of all country codes to reference in your calc to make it work internationally.

Hope this helps!