Field format for telephone number?

So my landline phone has a built-in blocklist capable of 250 entries. Because of the finite number of entries, I maintain a simple Excel spreadsheet to track:

  1. The number, entered as 1234567890, is automatically converted to (123) 456-7890.
  2. The city and state of the area code and prefix, which I manually look up and enter.

Obviously, a database is better suited for this than a spreadsheet, since there are no numbers to be crunched.

But is there an easy way to format a raw number into standard North American telephone format?


Pattern(phonenumber,"(###) ###-####")

Bruce’s answer will work, but Panorama X has a function that formats a raw number into North American telephone format.

To set up the ability to enter a number directly in the data sheet field as 1234567890 and have it display as (123) 456-7890 after entry, you simply put that pattern in the Field Properties panel for that field.


An alternate to my previous option is to have the Phone field set as text and use Jim’s formatphone( function in the formula of the Field Properties panel as shown below. This will make it possible for the fully formatted phone number to be output in a procedure or formula whereas the previous suggestion would return the unformatted number even though it is being displayed formatted in the data sheet.

Thank you for all replies. In particular, a BIG thank-you to Jim and Gary, whose combined suggestions have provided what I’m looking for!

One nice thing about the formatphone( function is that it will ignore any phone number that has more or less then ten digits. This makes it easy to include non North America phone numbers (though in that case you will have to supply the formatting yourself).

For your trivia collection - Australian mobile (cell) numbers have ten digits with the format 04** *** ***

One favor that I’ve typically offered to my users is a check for the number of digits. If they enter 7 digits, I use a default area code that the user has set. If it is 10 digits, I format as does formatPhone(. If it is more than 10 digits, I format it for the 10 digit standard, then add a ‘ext 234’ or such to allow for extensions.

For those tables that have international phone numbers, I check the ‘Country’ field in the table, and then format appropriately, though there are many that do not have absolute standards. Several countries have multiple length numbers within the country!