Dashes in Phone Numbers

[Rich Hartnett]

I have a table which was exported from an old FileMaker database table. It contains phone numbers, but there are no dashes inbetween the area code, exchange and number.

Is there an expression I could use that would automatically place a dash after the first three and second three digits?


3015551212 to this 301-555-1212

[AaronBBrown]

Re: Dashes in Phone Numbers

I have a table which was exported from an old FileMaker database table. It contains phone numbers, but there are no dashes inbetween the area code, exchange and number.

Is there an expression I could use that would automatically place a dash after the first three and second three digits?


3015551212 to this 301-555-1212

Something like this would work:
pn = left(pn,3)+"-"+substr(pn,4,3)+"-"+substr(pn,7,4)

You could write a regular expression to do it, but assuming all your numbers are indeed 10 digits, that will do the trick.

[Rich Hartnett]

Re: Dashes in Phone Numbers

Aaron I assume the "pn" is Phone Number (field).

When I substituted my phone number field name for the "pn's," it returned a logical response instead of a character response. Did I do something wrong?

[AaronBBrown]

Re: Dashes in Phone Numbers

Aaron I assume the "pn" is Phone Number (field).

When I substituted my phone number field name for the "pn's," it returned a logical response instead of a character response. Did I do something wrong?

Are you making a calculated field? If so, remove the "pn = ", otherwise it will be doing a comparison.

[Rich Hartnett]

Re: Dashes in Phone Numbers

Yeah sorry Aaron, I was making a calculated field.

So anyway, I got rid of the pn=

and using the remainder of the equation, I end up with this:

301--55-5-12

instead of:

301-555-1212

[AaronBBrown]

Re: Dashes in Phone Numbers

Sounds like you already have dashes in there.

Yeah sorry Aaron, I was making a calculated field.

So anyway, I got rid of the pn=

and using the remainder of the equation, I end up with this:

301--55-5-12

instead of:

301-555-1212

[Rich Hartnett]

Re: Dashes in Phone Numbers

Nope, just numbers. I even created a mini test table, with just one field in it, and filled it with 3015551212 in the first five records.

If I use the above calculation, and update the field, the numbers all end up as above:

301--55-5-12

Weird huh?

[Rich Hartnett]

Re: Dashes in Phone Numbers

Here's the table so you can see what I mean.

[AaronBBrown]

Re: Dashes in Phone Numbers

Nope, just numbers. I even created a mini test table, with just one field in it, and filled it with 3015551212 in the first five records.

If I use the above calculation, and update the field, the numbers all end up as above:

301--55-5-12

Weird huh?

Not sure what to tell you. My calculation has 2 "-" in it, yours has 4. That makes no sense.

pn = "7815551212"
? left(pn,3)+"-"+substr(pn,4,3)+"-"+substr(pn,7,4)
= "781-555-1212"

[Rich Hartnett]

Re: Dashes in Phone Numbers

Yeah I know it's strange.

Check out the attached screen shot. Other than the weird result, is there anything wrong with my calculation?

[AaronBBrown]

Re: Dashes in Phone Numbers

Yeah I know it's strange.

Check out the attached screen shot. Other than the weird result, is there anything wrong with my calculation?

Send a screen shot with just "Owners_Phone" as the expression.

[AaronBBrown]

Re: Dashes in Phone Numbers

Here's the table so you can see what I mean.

I need to see the table with the original values. What you sent is after-the-fact

[Rich Hartnett]

Re: Dashes in Phone Numbers

Okay try this one.

[AaronBBrown]

Re: Dashes in Phone Numbers

I don't know Rich. I created a new calculated field called "owners_phone_calc" with the calc expression I provided and, after a recalc, it worked fine.

[Rich Hartnett]

Re: Dashes in Phone Numbers

Well this time I did it by using the update function and assigning a calculated value to the field, and it worked fine!

Don't know why it wouldn't work as a field rule, but either way the problem is solved.

Thanks.