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

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered

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

Photo of Andrew

Andrew

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Try this

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

Andrew

  • 0 Points
Worked Perfectly - Thanks Mark!
Photo of David_In_Tucson

David_In_Tucson

  • 216 Points 100 badge 2x thumb
Thank you Mark.
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
)
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
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]
Photo of David

David

  • 836 Points 500 badge 2x thumb
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]), "-")
)
Photo of Cassie

Cassie

  • 182 Points 100 badge 2x thumb
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
Silly question, but are you sure you are looking at the right field for the result.  The one with the formula?
Photo of Cassie

Cassie

  • 182 Points 100 badge 2x thumb
Not at all. I have checked the formula, I have checked the formula of the field Im pulling from. 
Is the field type formula text?
Photo of Cassie

Cassie

  • 182 Points 100 badge 2x thumb
It's a phone number field. I was finally able to get Matthew Neil suggestion to work. Thank you so much for your help.
Right so you needed it to be text so it won’t put back the brackets :)