Autoformatting telephone numbers

[jocar]

I got the information below from a techinfo database. I like example three, but I am not sure how to replace the unformatted number with the formatted number. Any ideas...or better ideas?<br>
---------------<br>
<br>
FileMaker Pro does not have a built in number format for telephone numbers. There are two approaches you can take to get numbers formatted the way you want.<br>
<br>
Notes<br>
a) All calculations in this article use functions in FileMaker Pro 3.0. See the note at the end of the article regarding adapting it for earlier versions of FileMaker Pro.<br>
b) For clarity, a carat character ( ^ ) is used wherever there should be a space in a formula.<br>
c) All of the calculations should be set to return text, not a number. Setting it to a number will strip out the parentheses, spaces and dashes.<br>
<br>
Example 1<br>
All of the examples discussed in this article assume that you have the following field:<br>
<br>
PhoneNumber (text)<br>
<br>
The solution you use depends on how the telephone numbers are going to be entered. If the data is entered like this: 800 800 8123 , use the following calculations to format the field:<br>
<br>
For FileMaker Pro 4.0 and 3.0, you can either create a new calculation called PhoneNumber2 or use the replace function and replace the contents of PhoneNumber with the results of this calculation:<br>
<br>
&quot;(&quot; & LeftWords ( Phone Number,1 ) & &quot ^ &quot; & MiddleWords ( Phone Number, 2, 1) & &quot;-&quot; & RightWords (Phone Number, 1)<br>
<br>
For all versions of FileMaker Pro from 1.0 to 4.0:<br>
<br>
&quot;(&quot; & Left(Phone Number,3 ) & &quot^&quot; & Middle(Phone Number, 5, 3) & &quot;-&quot; & Right(Phone Number,4)<br>
<br>
Both of these calculations will return: (800) 800-8123.<br>
<br>
Example 2<br>
Most situations with phone numbers have the numbers entered differently depending on who is doing the data entry. If, for instance, some records are entered with area codes and some not, use the following calculations, (this example assumes that no parentheses have been added)<br>
<br>
If(Length(Phone Number) &lt; 8, Left (Phone Number,3) & &quot;-&quot; & Right (Phone Number,4), &quot;(&quot; & Left (Phone Number,3) & &quot^ &quot; & Middle(Phone Number,5,3) & &quot;-&quot; & Right(Phone Number,4))<br>
<br>
This will turn 800 800-8123 into (800) 800-8123 and 800-8123 or 800 8123 to 800-8123<br>
<br>
Note that this calculation checks the number of characters in the Phone Number field, and if there are more than 8 it assumes that there is an area code. <br>
<br>
Example 3<br>
The most complicated situation would be where numbers have been entered all kinds of different ways, in some situations you may not be able to test for them all. The key to remember is to count the number of characters and write the appropriate test and solution.<br>
<br>
(800) 800-8954 is a total of 14 characters<br>
800-800-8954 is 12 characters<br>
8008008954 is 10 characters<br>
727-9054 is 8 characters<br>
727 9054 is also 8 characters<br>
408 727-9054 x4007 is 18 characters<br>
<br>
The calculation would be similar to the one in Example 2:<br>
<br>
If(Length(PhoneNumber) &lt; 9, Left(PhoneNumber,3) & &quot;-&quot; & Right(PhoneNumber,4), If(Length(PhoneNumber)=10, &quot;(&quot; & Left(PhoneNumber,3) & &quot^&quot; & Middle(PhoneNumber, 4, 3) & &quot;-&quot; & Right(PhoneNumber,4),<br>
<br>
If(Length(PhoneNumber)=12, &quot;(&quot; & Left(PhoneNumber,3) & &quot^&quot; & Middle(PhoneNumber, 5, 3) & &quot;-&quot; & Right(PhoneNumber,4), <br>
<br>
If(Length(PhoneNumber)=14, PhoneNumber, <br>
<br>
If(Length(PhoneNumber)=18, &quot;(&quot; & Left(PhoneNumber,3) & &quot^&quot; & Middle(PhoneNumber, 4, 3) & &quot;-&quot; & Middle(PhoneNumber,9,4) & &quot; ext&quot; & Right(PhoneNumber,4),<br>
PhoneNumber)))))<br>
<br>
Note: In FileMaker Pro you can put returns in your calculations as above to make the calculation look cleaner. These returns will not effect the outcome of the calculation.