Custom Field Formulas: Phone Numbers

The basics of creating a custom field using a formula

For best results, begin with a custom view:

1. In Outlook 2010 and up, look on the View ribbon for Change View. Expand it and choose Manage Views. In Outlook 2007 and older, go to the View menu, find the menu for Define views.
2. Click New.
3. 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.
4. Remove any columns (fields) you don't need in the view or print out and Add the fields you want to include in the view.
5. Click the New Columnbutton (It's New Field in older versions of Outlook).
   
6. Enter a name for the field and select Formula from the Type drop down (or press F after tabbing out of the name field)
   
7. You can type or paste the formula in the Formula field and click Ok or click the Edit button to bring up the Formula Field editor.
   
8. If you know the field names or formula, type them in, or use the Field button to select and enter field names into formulas and the Function button to select functions for your formula.
   
9. Make any other changes to the view as desired and press Ok to exit the screens and the Apply the view.

The formula is the example above is from "Create a Custom View to Show the 'Remind at' Time"

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.

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.