FixPhoneNumberFormatUS ( Number )

Customizable function to standardize raw phone number input to 123-456-7890 x123

Be the first to rate this function Log in to vote

RealGrouchy - Show more from this author
https://realgrouchy.blogspot.com

Sample input:

FixPhoneNumberFormatUS ( "+1-(321)-456.7890 ,,,*xabcdef,, 123" )

Sample output:

321-456-7890 x123

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

/* FixPhoneNumberFormat V2.1 by RealGrouchy

This custom function is used to standardize the formatting of a 7- or 10-digit North American* phone number input by changing the format to "321-456-7890 x123". (*non-North American phone numbers are left intact.)

This is a calculation adapted for a Custom Function, based on the Auto-Enter calculation I posted in Feb 2015 at https://community.filemaker.com/thread/125368 (no longer exists) which was to be entered into the Auto-Enter Calculation Value for phone number fields. There is commented code at the end that can be added into the field validation. The prevous version built significantly on code supplied by philmodjunk at https://community.filemaker.com/message/326770#326770

This version handles some additional situations and turns it into a custom function.

2018-04-03: V2.1: If the number is all zeros, return empty result (instead of +000000000)

*/

Let (
               
[
/* 
CUSTOMIZATIONS:
- Default Area Code: Change 321 below to update the default area code.
- To not add area codes to 7-digit numbers, remove or comment out the line that starts with "Length ( P1 ) = 7 ; … "
- Extension Separator: e.g. for "321-456-7890 ext. 123", replace " x" with " ext. " (with spaces and quotes) (default format is 321-456-7890 x123). 
- To change the phone number format you'll have to tweak the results in the first two lines of the "Case" function near the end
*/

                       /* LocalAreaCode = 321 ; */  //must be numbers only
                        xFormat = " x" ;
                        xFiltercharacters = "\/*#," ; //all numbers after the first letter *or one of these characters* are considered an extension


/* 
EXAMPLE INPUTS/OUTPUTS:

INVALID RESULTS DUE TO LETTERS IN OR BEFORE PHONE NUMBER:
The one situation where data loss may occur is if someone uses letters instead of numbers for their number, deliberately or by accident. See the section on International phone numbers for more info on how non-7 or 10 digit numbers are handled.
    1 (800) 555-SMRT —> 1-800-555 (Interpreted as international number, letters stripped)
    800-555-SMRT (7678) —> +800-555 x7678 (interprets as 6-digit international number, numbers after letter treated as extension)
    321h456-7890 —> 321 x4567890 (interprets as 6-digit international number, numbers after letter treated as extension)
    (321) 456-o789 —> 321-456 x789 (interprets as 6-digit international number, numbers after letter treated as extension)

TEN-DIGIT PHONE NUMBERS:
Any string with 10 numbers and no letters will return in format 321-456-7890, including:
Any similar string with 11 numbers that starts with a 1 or +1 will have that part stripped off
    3124567890
    (321) 456-7890
    321.456.7890
    321 456 7890
    (321)- 456-7890
    321)456-7890
    321-456-78-90
    1 (321) 456-7890
    1-321-4567890
    +1-(321)-456.7890
    13214567890
    —————————
    = 321-456-7890

SEVEN-DIGIT PHONE NUMBERS:
Any string with 7 numbers and no letters will add an area code and convert to format 312-456-7890, including:
This can be disabled by removing or commenting out the line that starts with "Length ( P1 ) = 7 ; …"
    4567890
    456-7890
    (etc.)
    —————————
    = 321-456-7890

MISC: 
Any text will be stripped if there are no numbers after it:
    321-456-7890 (cell)
    456-7890 - sister
    —————————
    = 321-456-7890

All-zero numbers will return an empty result:
    000-000-0000
    —————————
    = ""

PHONE EXTENSIONS:
Any numbers that come after any text (where 'text' includes letters and the characters in the xFiltercharacters variable above, i.e. /, \, #, comma, or *) will be interpreted as an extension in the format 321-456-7890 x123 and any non-numbers will be stripped (extension separator can be customized by changing the xFormat variable above):
    321-456-7890x123 (home)
    321-456-7890*123
    321-456-7890 # 123
    321-456-7890 ,,,*xabcdef,, 123
    321-456-7890 p.123
    1321-456-7890 ext. 123
    +1-321-456-7890 local 123
    456-7890 ext. 123
    456780/123
    —————————
    = 321-456-7890 x123

    The exception to this is if there are no numbers before the first character. If that's the case ALL numbers after the first character are treated as the phone number (no extension)
    (cell) (321) 456-7890  —> 321-456-7890
    work: 321-456-7890 ext. 123 —> 321-456-7890-123 (treats as international)

    NOTE: the following scenarios may not work as intended:
    (321) 456-7890 (cell) —> 321-456-7890
    (cell) (321)456-7890 
    321-456-7890 / 800-456-7890 —> 321-456-7890 x8004567890
    321-456-7890 press 1 x 234 —> 321-456-7890 x1234
    (321) 456-7890 1234 —> 321-456-7890-1234 (interpreted as international number)
    321h456-7890 —> 321 x4567890 (interprets 3 numbers before letter as international number)
    1234 (office) —> "1234" (interpreted as international number with characters before first letter unchanged)

NON-CANADA/US ("INTERNATIONAL") PHONE NUMBERS:
A number will be treated as International if it starts with + (except +1) or doesn't have 7 or 10 characters (or 11 starting with 1).
This will substitute any non-numbers after the + and before the extension into hyphens, and will strip out any non-numbers from the extension:
    011-01-234-567-890  —> Unchanged
    +44 012345.67890 —> +44-012345-67890
    +(44) 0123-4567-890- —> +44-0123-4567-890
    12(345) 6789 0123 —> 12-345-6789-0123
    (21)-34  —> 21-34
    21.34 poste 3(11) —> 21-34 x311
    1234 (cell) —> 1234
    
    NOTE: If an international phone number has 7 or 10 digits but does not start with +, it will be interpreted as North American!
    64 9 123 4567 —> 649-123-4567
    +1-321-456-7890 —> 321-456-7890 

    NOTE: If there are a lot of non-number characters, the calculation will only appear any that are found in the first 20 non-number characters:
    123()()()()()()()()()456 —> 123-456 (because both "(" and ")" appear in the first 20 non-number characters
    123()()()()()()()()()()()()()()..456 —> 123-..456 (because "." does not appear in the first 20 non-number characters. If your input has this many non-number characters you have a very strange phone number input!)

*/

               
/* Continuation of Let function: */
               
// e is a variable to denote where the first letter is (if any) in the phone number field (i.e. to find the extension)
                         e = Position ( Number ; Left ( Filter ( Number ; "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" & xFiltercharacters ) ; 1 )  ; 1 ; 1 ) ;
               
// P1a is a variable that returns anything before the first letter (i.e. should be the phone number only), otherwise the whole field if no letters, or if letters before any numbers.
                         P1a = If ( e and ( Length ( Filter ( Left ( Number ; e -1 ) ; "1234567890" ) ) > 0 ) ; Left ( Number ; e - 1 ) ; Number ) ;
               
// P1b is a variable that filters the phone number to strip out anything that isn't a number
                         P1b = Filter ( P1a ; 1234567890 ) ;

// P1 is a variable that strips out a 1 at the start 
                         P1 = Right ( P1b ; Length ( P1b ) - If ( Left ( P1b ; 1 ) = 1 ; 1 ; 0 ) ) ;

// Intl is a variable that returns TRUE if the input phone number starts with a +, UNLESS it starts with +1
                         Intl = ( Left ( P1a ; 1 ) = "+" ) and ( Left ( P1a ; 2 ) ≠ "+1" ) ;

// P1int is a variable that substitutes non-numbers with hyphens in international phone numbers (prior to the extension)
                         P1intfilter = Filter ( P1a ; Substitute ( P1a ; [ "1" ; "" ] ; [ "2" ; "" ] ; [ "3" ; "" ] ; [ "4" ; "" ] ; [ "5" ; "" ] ; [ "6" ; "" ] ; [ "7" ; "" ] ; [ "8" ; "" ] ; [ "9" ; "" ] ; [ "0" ; "" ] ) ) ; // Finds which non-number characters need to be substituted (letters don't need to be substituted because they trigger the extension)
                         P1intsub = Substitute ( P1a ; [ Middle ( P1intfilter ; 1 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 2 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 3 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 4 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 5 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 6 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 7 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 8 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 9 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 10 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 11 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 12 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 13 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 14 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 15 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 16 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 17 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 18 ; 1 ) ; "-" ] ; [ Middle ( P1intfilter ; 19 ; 1 ) ; "-" ] ; [ "-------" ; "-" ] ; [ "------" ; "-" ] ; [ "-----" ; "-" ] ; [ "----" ; "-" ] ; [ "---" ; "-" ] ; [ "--" ; "-" ] ) ; // Swaps out the found characters with hyphens, then attempts to concatenate hyphens to only one in a row
                         P1inttrail = Left ( P1intsub ; Length ( P1intsub ) - If ( Right ( P1intsub ; 1 ) = "-" ; 1 ; 0 ) ) ; // strip out trailing hyphens
                         P1int = Substitute ( Right ( P1inttrail ; Length ( P1inttrail ) - If ( Left ( P1inttrail ; 1 ) = "-" ; 1 ; 0 ) ) ; "+-" ; "+" ) ; // strip out leading hyphens

               
// P2a is a variable that assumes anything after the first letter is the extension and returns only the numeric portion
                         P2a = If ( e and ( Length ( Filter ( Left ( Number ; e -1 ) ; "1234567890" ) ) > 0 ) ; Filter ( Right ( Number ; Length ( Number ) - Length ( P1a ) ) ; 1234567890 ) )  ;
               
// P2 is a variable that returns the properly formatted extension, or nothing if there are no numbers in the extension.
                         P2 = If ( IsEmpty ( Filter ( P2a ; 1234567890 ) ) ; "" ; xFormat & P2a ) ;

//  z is a variable that indicates that the numeric portion of the input is all zeros.
                         z = Filter ( Number ; "0123456789" ) = Filter ( Number ; "0" )

               ];
               
// Case evaluates whether the length of the numeric portion before any letters is 7 or 10 digits and parses the phone number accordingly, appending the extension separated by a space.
                        Case ( Length ( P1 ) = 10 and not ( z or Intl ) ; Left ( P1 ; 3 ) & "-" & Middle ( P1 ; 4 ; 3 ) & "-" & Right ( P1 ; 4 ) & P2 ;
               
// (Case continued) Adds local area code if not entered. Remove or comment out this line if you don't want to convert 7-digit numbers with a common area code.
   //                                Length ( P1 ) = 7 and not Intl ; Filter ( LocalAreaCode ; "1234567890" ) & "-" & Left ( P1 ; 3 ) & "-" & Right ( P1 ; 4 ) & P2 ;


// (Case continued) If the input is all zeros, return an empty result
                      z ; "" ;                
// (Case continued) If the numeric portion isn't 7 or 10 digits long or starts with a + (but not +1), it returns the number as-is with formatted extension. (Validation should throw a warning to the user in case they erroneously entered the wrong number of digits)

                      If ( IsEmpty ( P1int & P2 ) ; "" ; "+" ) & P1int & P2
               
                  ) // case
               
        ) // let
               

               /* Use the following in the validation calculation for this field:

               //See Auto-enter calculation for more notes on how this works and what it does
               Let (
               [ e = Position ( Number ; Left ( Filter ( Number ; "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" ) ; 1 )  ; 1 ; 1 ) ;
                         P1a = If ( e ; Left ( Number; e -1 ) ; Number ) ;
                         P1 = Filter ( P1a ; 1234567890 )
                         ] ;
                        ( Length ( P1 ) = 10 ) or ( Length ( P1 ) = 7 )
               )
               //Use this as custom error text in validation: "Phone number is not a valid North American phone number.  Use anyway? (Click no to edit)"
               */

Garbage in, phone number out. This custom function I built to standardize phone number inputs has served me well for over 10 years.

This custom function is used to standardize the formatting of a 7- or 10-digit Canada/US* phone number input by changing the format to "321-456-7890 x123". (*non-Canada/US phone numbers are left intact.)

The extension separator can be customized (x by default), and if most of your contacts are local you can add a default local area code (disabled by default).

The following examples are included in the script comments:

EXAMPLE INPUTS/OUTPUTS:

INVALID RESULTS DUE TO LETTERS IN OR BEFORE PHONE NUMBER:
The one situation where data loss may occur is if someone uses letters instead of numbers for their number, deliberately or by accident. See the section on International phone numbers for more info on how non-7 or 10 digit numbers are handled.
1 (800) 555-SMRT —> 1-800-555 (Interpreted as international number, letters stripped)
800-555-SMRT (7678) —> +800-555 x7678 (interprets as 6-digit international number, numbers after letter treated as extension)
321h456-7890 —> 321 x4567890 (interprets as 6-digit international number, numbers after letter treated as extension)
(321) 456-o789 —> 321-456 x789 (interprets as 6-digit international number, numbers after letter treated as extension)

TEN-DIGIT PHONE NUMBERS:
Any string with 10 numbers and no letters will return in format 321-456-7890, including:
Any similar string with 11 numbers that starts with a 1 or +1 will have that part stripped off
3124567890
(321) 456-7890
321.456.7890
321 456 7890
(321)- 456-7890
321)456-7890
321-456-78-90
1 (321) 456-7890
1-321-4567890
+1-(321)-456.7890
13214567890
—————————
= 321-456-7890

SEVEN-DIGIT PHONE NUMBERS:
Any string with 7 numbers and no letters will add an area code and convert to format 312-456-7890, including:
This can be disabled by removing or commenting out the line that starts with "Length ( P1 ) = 7 ; …"
4567890
456-7890
(etc.)
—————————
= 321-456-7890

MISC:
Any text will be stripped if there are no numbers after it:
321-456-7890 (cell)
456-7890 - sister
—————————
= 321-456-7890

All-zero numbers will return an empty result:
000-000-0000
—————————
= ""

PHONE EXTENSIONS:
Any numbers that come after any text (where 'text' includes letters and the characters in the xFiltercharacters variable above, i.e. /, \, #, comma, or *) will be interpreted as an extension in the format 321-456-7890 x123 and any non-numbers will be stripped (extension separator can be customized by changing the xFormat variable above):
321-456-7890x123 (home)
321-456-7890*123
321-456-7890 # 123
321-456-7890 ,,,*xabcdef,, 123
321-456-7890 p.123
1321-456-7890 ext. 123
+1-321-456-7890 local 123
456-7890 ext. 123
456780/123
—————————
= 321-456-7890 x123

The exception to this is if there are no numbers before the first character. If that's the case ALL numbers after the first character are treated as the phone number (no extension)
(cell) (321) 456-7890 —> 321-456-7890
work: 321-456-7890 ext. 123 —> 321-456-7890-123 (treats as international)

NOTE: the following scenarios may not work as intended:
(321) 456-7890 (cell) —> 321-456-7890
(cell) (321)456-7890
321-456-7890 / 800-456-7890 —> 321-456-7890 x8004567890
321-456-7890 press 1 x 234 —> 321-456-7890 x1234
(321) 456-7890 1234 —> 321-456-7890-1234 (interpreted as international number)
321h456-7890 —> 321 x4567890 (interprets 3 numbers before letter as international number)
1234 (office) —> "1234" (interpreted as international number with characters before first letter unchanged)

NON-CANADA/US ("INTERNATIONAL") PHONE NUMBERS:
A number will be treated as International if it starts with + (except +1) or doesn't have 7 or 10 characters (or 11 starting with 1).
This will substitute any non-numbers after the + and before the extension into hyphens, and will strip out any non-numbers from the extension:
011-01-234-567-890 —> Unchanged
+44 012345.67890 —> +44-012345-67890
+(44) 0123-4567-890- —> +44-0123-4567-890
12(345) 6789 0123 —> 12-345-6789-0123
(21)-34 —> 21-34
21.34 poste 3(11) —> 21-34 x311
1234 (cell) —> 1234

NOTE: If an international phone number has 7 or 10 digits but does not start with +, it will be interpreted as North American!
64 9 123 4567 —> 649-123-4567
+1-321-456-7890 —> 321-456-7890

NOTE: If there are a lot of non-number characters, the calculation will only appear any that are found in the first 20 non-number characters:
123()()()()()()()()()456 —> 123-456 (because both "(" and ")" appear in the first 20 non-number characters
123()()()()()()()()()()()()()()..456 —> 123-..456 (because "." does not appear in the first 20 non-number characters. If your input has this many non-number characters you have a very strange phone number input!)

Comments

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

FixPhoneNumberFormatUS ( Number )

Comments

Support this website.

Support this website.