title: Microsoft Access: Strip phone numbers from feed that are all over the place
body: <p>In Microsoft Access, I am receiving a feed of phone numbers which need to be processed and conformed into a standard format for post-processing in another system. The numbers I am given are all over the place. They are international from many countries, may include special characters, spaces, parens, letters, some have extensions while other don't and not all have area codes. Despite all of this I need to format it equally the same across all numbers. Here is what I am looking at...</p>
  
  <pre><code>Ex.
  
  +1_(860)_5666344_x_4454  &lt;--- All _'s are spaces in the string
  
  Convert To
  
  8605666344
  </code></pre>
  
  <p>You see, we have special considerations to account for such as:</p>
  
  <p>1) International numbers, the country code could be anything. So for instance a US country code of <code>+1</code> but an area code of <code>246</code> may look like it is a country code of <code>+1 246</code> for Barbados when in reality it is a USA area code of <code>(246)</code>. I need to account for this</p>
  
  <p>2) I need to remove all special characters, spaces, letters (extension X), parens</p>
  
  <p>3) I need to remove any extensions, which are always prepended with an <code>x</code> although not always having a space after it.</p>
  
  <p>4) In some cases the area code my not even be included. Despite this the other rules must be enforced (no extensions, no special characters and no country code and no spaces)</p>