Spreadsheet formula to correctly format telephone numbers

24 Feb 2009 0 Comments

When working in spreadsheet software such as Apple Numbers or OpenOffice.org Calc, you can easily remove formatting characters (brackets, dashes, spaces, etc.) from and add leading zeroes to multiple telephone numbers using a formula.

In this example, I’m assuming that your telephone numbers are all on the A Column. Starting with the cell in the B Column immediately adjacent to the first telephone number in the A Column, enter this formula (update the ‘A2‘ below as needed):

=substitute(substitute(substitute(substitute
  (substitute(substitute(substitute(substitute
  (A2, "/", ""), ".", ""), " ", ""), "(", ""), ")", ""),
  "-", ""), "+", ""), "&", "")

… then highlight the B Column cells downwards from this cell until you reach the row containing the last telephone number in the A Column. In Numbers, click the ‘Insert‘ option in the menubar and choose ‘Fill‘->’Fill Down‘. In OpenOffice.org, click the ‘Insert‘ option in the menubar and choose ‘Fill‘->’Down‘.

To automatically add leading zeroes to telephone numbers when they are formatted, follow the steps above but use the following formula instead (update the ‘A2‘ below as needed):

=if(mid(A2,1,1) = "0",substitute(substitute(substitute
  (substitute(substitute(substitute(substitute
  (substitute(A2, "/", ""), ".", ""), " ", ""), "(", ""),
  ")", ""), "-", ""), "+", ""), "&", ""),
  concatenate("0", substitute(substitute
  (substitute(substitute(substitute(substitute
  (substitute(substitute(A2, "/", ""), ".", ""), " ",
  ""), "(", ""), ")", ""), "-", ""), "+", ""), "&", "")))