Calculating a new phone number format

[Gailseiu49]

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"]

[slstrother]

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)

[DykstrL]

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.

[jasonwood]

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