Discussions

Expand all | Collapse all

Converting a QB entered/formatted Phone number to a 10-digit number

Andrew Andrew03-28-2016 12:19

David_In_Tucson David_In_Tucson10-11-2016 23:55

  • 1.  Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 03-28-2016 04:17

    I have a Phone Number Field that the user enters a phone number into. QB returns with a formatted number in the following format:

    (555) 555-1212

    I am using this number in an Email field to send an SMS text message using my e-mail program. I need to strip out the spaces, the parenthesis, and the hyphen to get

    5555551212

    Brain dead tonight; I am sure someone has a quick answer to this one.

    Thanks for your help.

    Andy



  • 2.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 03-28-2016 05:59
    Try this

    List("",
    Part([Phone],1, "() -"),
    Part([Phone],2,  "() -"),
    Part([Phone],3,  "() -"),
    Part([Phone],4,  "() -"),
    Part([Phone],5,  "() -"),
    Part([Phone],6,  "() -"))


  • 3.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 03-28-2016 12:19
    Worked Perfectly - Thanks Mark!


  • 4.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 10-11-2016 23:55
    Thank you Mark.


  • 5.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 03-27-2017 21:11
    Thank was helpful, Mark. I modified it to use with phone extensions and to create a field that would allow me to require phone numbers to be unique:

    // Created 3/27/2017 by Phillip Dennis Watkyn LLC
    // To convert a phone number to a text field that will allow a unique constraint

    // Pad the value for an unknown number of digits in a phone extension
    // Phone numbers without extension will usually be 14 characters (000) 000-0000
    // With a four digit extension, can be as much as 20 characters (000) 000-0000 x0000
    // But as soon as any extension is added, the length jumps from 14 to 17
    // Numbers under 15 digits get padded with Fs at the end until they reach a length of 20 characters - was going to use 0s but those could be real extensions
    // Numbers from 17 to 19 digits get enough Fs to reach a length of 20

    var number phoneNumberLength = Length( [Phone Number] );
    var text padding = Case ( $phoneNumberLength, 14, "FFFFFF", 17, "FFF", 18, "FF", 19, "F", "" );

    List("",
    Part([Phone Number],1, "() -x"),
    Part([Phone Number],2,  "() -x"),
    Part([Phone Number],3,  "() -x"),
    Part([Phone Number],4,  "() -x"),
    Part([Phone Number],5,  "() -x"),
    Part([Phone Number],6,  "() -x"),
    Part([Phone Number],7,  "() -x"),
    Part([Phone Number],8,  "() -x"),
    Part([Phone Number],9,  "() -x"),
    Part([Phone Number],10,  "() -x"),
    Part([Phone Number],11,  "() -x"),
    Part([Phone Number],12,  "() -x"),
    Part([Phone Number],13,  "() -x"),
    $padding
    )


  • 6.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Top
    Contributor
    Posted 03-28-2017 04:07
    Or if you want to keep it simple:

    Left((NotLeft([Customer Cell Phone], "(")),")")&
    Right((NotRight([Customer Cell Phone], "-")),3)&
    Right([Customer Cell Phone], "-")
    &"@"&
    [Provider - Text Domain]


  • 7.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

     
    Posted 12-28-2017 15:05
    Here is my further improvement on this for "international format" using a QB phone-number field as a source. This strips out extensions, uses an international prefix if there is one, applies country code for North America.

    If([Phone]<>"",
    If(NotRight([Phone], "(")<>"","+"&Trim(NotRight([Phone], "(")),
    Case([Country],
    "United States","+1",
    "","+1",
    "Canada","+1",
    "Mexico","+52",
    "")))
    &
    Left((NotLeft([Phone], "(")),")")&
    Right((NotRight([Phone], "-")),3)&
    Right(
    If(Contains([Phone],"x"),NotRight([Phone],"x"),[Phone]), "-")
    )


  • 8.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 08-16-2018 22:34
    I can not for the life of me get this to work. 

    List("",
    Part([Guardian-Cell Number],1, "() -"),
    Part([Guardian-Cell Number],2,  "() -"),
    Part([Guardian-Cell Number],3,  "() -"),
    Part([Guardian-Cell Number],4,  "() -"),
    Part([Guardian-Cell Number],5,  "() -"),
    Part([Guardian-Cell Number],6,  "() -"))

    Striped Cell Number
    (555) 555-1212


  • 9.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 08-16-2018 22:40
    Syntax error or wrong results?


  • 10.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 08-16-2018 23:25
    No error. I still get the (555)555-1212 and not 5555551212.


  • 11.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 08-16-2018 23:50
    Silly question, but are you sure you are looking at the right field for the result.  The one with the formula?


  • 12.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 08-17-2018 00:19
    Not at all. I have checked the formula, I have checked the formula of the field Im pulling from. 


  • 13.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 08-17-2018 00:53
    Is the field type formula text?


  • 14.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 08-17-2018 01:07
    It's a phone number field. I was finally able to get Matthew Neil suggestion to work. Thank you so much for your help.


  • 15.  RE: Converting a QB entered/formatted Phone number to a 10-digit number

    Posted 08-17-2018 11:09
    Right so you needed it to be text so it won�t put back the brackets :)