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...


+1_(860)_5666344_x_4454  <--- All _'s are spaces in the string

Convert To


You see, we have special considerations to account for such as:

1) International numbers, the country code could be anything. So for instance a US country code of +1 but an area code of 246 may look like it is a country code of +1 246 for Barbados when in reality it is a USA area code of (246). I need to account for this

2) I need to remove all special characters, spaces, letters (extension X), parens

3) I need to remove any extensions, which are always prepended with an x although not always having a space after it.

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)

2 Answers

June7 On

Consistency in data structure is critical in string manipulation. Build a VBA custom function to process this data.

For a start, try a nesting of Replace() function to remove parens, s represents the example data string:


Take that value and run through If Then block to test for the "x" character and if present truncate string. Then another If Then to test for the "+" character and if present remove the prefix.

Use InStr, InStrRev, Mid, Left, Right, Len string manipulation functions. Another Replace() can remove spaces or use Val().

A very simple code example based on the given sample data:

Function ExtractPhoneNum(s As String) As String
Dim strS As String
strS = Replace(Replace(s, "(", ""), ")", "")
If InStr(strS, "x") <> 0 Then strS = Left(strS, InStr(strS, "x") - 1)
If InStr(strS, "+") <> 0 Then strS = Mid(strS, InStr(strS, " ") + 1)
ExtractPhoneNum = Val(strS)
End Function
Gustav On

You can clean up the numbers in one line:

Phone = "+1 (860) 566-6344 x4454"
CleanPhone = Split(Replace(Replace(Replace(Replace(t, " ", ""), "(", ""), ")", ""), "-", ""), "x")(0)

CleanPhone -> "+18605666344"

But the international dialing code, which is present if the first character is "+", can take from 1 to 4 digits. Thus, you must look up a table of these to find the match. These can be found at many sources - one is here:

Wikipedia: List of country calling codes