# Discussions

View Only

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

• #### 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.

Andy

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

Top
Contributor
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"),
)

• #### 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",
"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

Top
Contributor
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

Top
Contributor
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

Top
Contributor
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

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