Custom Field Formulas: Phone Numbers

The basics of creating a custom field using a formula

For best results, begin with a custom view:

From the View menu, find the menu for Define views. In Outlook 2010, look on the View Ribbon for Change View. Expand it and choose Manage Views.
Click New.
Type in a Name for the new view and select Table view. Choose who can use the view and which folders it can be used in then click Ok.
Remove any fields you don't need in the view or print out and Add the field you want to work with.
Click the New Field button.
Enter a name for the field and select Formula from the Type drop down (or press F after tabbing out of the name field)
You can type or paste the formula in the Formula field and click Ok or click the Edit button will bring up the Formula Field editor. Use the Field button to select and enter field names into formulas and the Function button to select functions for your formula.
Make any other changes to the view as desired and press Ok to exit the screens and the Apply the view.

Phone Number Formula

Display a phone number as a 7-digit number - for example, you want +1 (202) 222-3456 to display as 2223456

Left(Right([Business Phone],8),3)+Right([Business Phone],4)

How it works: Getting the last 4 digits from the number is easy: Right([Business Phone],4) returns 3456 but getting the 3 numbers from the middle is more difficult because we need get rid of the leading numbers, which may or may not include the country code. Because the numbers we need will always be the last 8 characters (including the dash), we can count in from the right: Right([Business Phone],8) to return 222-3456 then get the first 3 numbers in this string: Left(Right([Business Phone],8),3), which reads the left 3 characters of 222-3456 or 222. The + joins (concatenates) the 222 and 3456 together, giving us 2223456.

screenshot of custom form in contacts folder

Note: If you use extensions with the phone numbers, +1 (202) 222-3456 x1234, this formula will not work. You'll need a more complicated formula that finds the x and counts back from it.

You can't use this method to remove the country or area codes from numbers to sync with PDAs. This method is useful if you need the data in other applications - Use Custom View Data in Another Program.