# Calculating a new phone number format

This topic is 7827 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

• Newbies

I have a "Phone" field in the format (xxx) xxx-xxxx. I need to convert this to the format xxx-xxx-xxxx. What calculation will net me this result? [color:"blue"]

##### Share on other sites

There are a couple of ways to go about this. If the data in your phone field is always formatted the same (xxx) space xxx-xxxx then the following calc will work

Middle(Phone,2,3)&"-"&Middle(Phone,7,3)&"-"&Right(Phone,4)

If the data is not formatted the same for all records, then you would need to remove the parens, spaces and hyphens. The following calc would work if this is the case.

Left(Substitute(Substitute(Substitute(Substitute(Phone, "(" , "" ),")","")," ",""),"-",""),3)&"-"&

Middle(Substitute(Substitute(Substitute(Substitute(Phone, "(" , "" ),")","")," ",""),"-",""),4,3)&"-"&

Right(Substitute(Substitute(Substitute(Substitute(Phone, "(" , "" ),")","")," ",""),"-",""),4)

##### Share on other sites

Use 2 fields - one PhoneEnter (text), the other a calculation field:

Middle(NumToText(Abs(TextToNum(PhoneEnter)*10000000)), 1, 3) & "-" & Middle(NumToText(Abs(TextToNum(PhoneEnter)*10000000)), 4, 3) & "-" & Middle(NumToText(Abs(TextToNum(PhoneEnter)*10000000)), 7, 4)

the result is Text.

Changing the data in PhoneEnter to a number strips out any symbols such as (, ) -, etc. then reformats the number into text formatted any way you want it to look.

##### Share on other sites

What if you wanted to search by this field? Wouldn't have to know how the field was entered in order to search for it? I think it might be better to run a script to strip the symbols/spaces so that in the PhoneEnter field the number looks like 5555555555, then do as you suggest to add in formatting for a display field. This way, in future you will only enter phone numbers as numbers, and then format them however you like.

So I guess the calculation would then look like...

Middle(NumToText(PhoneEnter), 1, 3) & "-" & Middle(NumToText(PhoneEnter), 4, 3) & "-" & Middle(NumToText(PhoneEnter), 7, 4)

One thing I did not understand... why did you multiply TextToNum(PhoneEnter) by 10000000? I'm a little new to text manipulation...

##### Share on other sites

This topic is 7827 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account

• ### Similar Content

• #### Filemaker 6 for Mac issue.

By Tumma K,

• (and 4 more)

Tagged with:

• 8 replies
• 4,390 views
• #### FM3 upgrade to FM Pro 5.5 - Windows 8 installation possible?

By MrEddByrnes,

• (and 4 more)

Tagged with:

• 5,191 views
• #### Moving data from fp5 to fp13

By bmill,

• (and 4 more)

Tagged with:

• 2 replies
• 3,438 views
• #### Filemaker 5.5 database will not delete records on-line

By randyinla,

• (and 4 more)

Tagged with:

• 3,066 views
• #### Are there still hosting providers for filemaker 5.5?

By randyinla,

• (and 4 more)

Tagged with:

• 2 replies
• 2,703 views
×
×
• Create New...

## Important Information

By using this site, you agree to our Terms of Use.