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

• 0
• Question
• Updated 10 months ago

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.

Andy

• 0 Points

Posted 3 years ago

• 0
• 70,444 Points
Try this

List("",
Part([Phone],1, "() -"),
Part([Phone],2,  "() -"),
Part([Phone],3,  "() -"),
Part([Phone],4,  "() -"),
Part([Phone],5,  "() -"),
Part([Phone],6,  "() -"))
• 0 Points
Worked Perfectly - Thanks Mark!
• 216 Points
Thank you Mark.
• 690 Points
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"),
)
• 31,698 Points
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]
• 836 Points
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",
"Mexico","+52",
"")))
&
Left((NotLeft([Phone], "(")),")")&
Right((NotRight([Phone], "-")),3)&
Right(
If(Contains([Phone],"x"),NotRight([Phone],"x"),[Phone]), "-")
)
• 182 Points
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
• 69,994 Points
Syntax error or wrong results?
• 182 Points
No error. I still get the (555)555-1212 and not 5555551212.
• 69,994 Points
Silly question, but are you sure you are looking at the right field for the result.  The one with the formula?
• 182 Points
Not at all. I have checked the formula, I have checked the formula of the field Im pulling from.
• 69,994 Points
Is the field type formula text?
• 182 Points
It's a phone number field. I was finally able to get Matthew Neil suggestion to work. Thank you so much for your help.
• 69,994 Points
Right so you needed it to be text so it won’t put back the brackets :)