Jump to content
Server Maintenance This Week. ×

Calculating a new phone number format

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

Recommended Posts

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


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)

Link to comment
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.

Link to comment
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...

Link to comment
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

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

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