Uniform Formatting

[darkstone3]

I currently have a database with a field phone numbers however there are roughly 10000 records and 45-50 different entry styles.

I have now set up an input mask however I amk wondering how I could automate a clean up of them into the standard !(###) ###-####

[missinglinq]

How could you possibly have 45-50 different ways of entering telephone numbers?

Linq ;0)>

[OldBirdman]

If it were I, I would not automate it. I would do it manually. To automate will require writing and debugging a fairly complex algorithm to handle 10-50 variations.

But by manually I don't mean 1 row (1 phone #) at a time.

Step 1: Make a copy of the table with name xxxCopy1
Step 2: Open the original table in datasheet view.
  Click in the phone # column
  Open the Find/Replace dialog(cntl-H).
  Click the Replace tab.
  Replace all occurrances of "(" with "" (zero-length String)
Step 3: Replace all occurrances of ")" with ""
Steps 4-50 Replace all other special characters with ""
  This may include []{},./-_ and anything else except the alphabet and number set
Step 51: Make a copy of the table with name xxxCopy2
Step 52: Change all occurrances of " "(spaces) with ""
Step 53: Make a copy of the table with name xxxCopy3
Note:
  All phone numbers are now ####### or ##########, unless some have leading 1's (i.e. 1-### ###-####) or overseas/foreign numbers.
Step 54:   Write a SELECT query WHERE LEFT(Phone#,1)="1" to see if any leading 1's.  If so, change to an UPDATE query and replace [Phone#] with MID([Phone#],2)
Step 55: Make a copy of the table with name xxxCopy4
Step 56: Write a SELECT query WHERE LEN([Phone#])=7 to see if any Phone# without AreaCode.  If so, change to an UPDATE query and replace [Phone#] with "303" & [Phone#] NOTE-USE YOUR DEFAULT AREACODE, not 303
Step 57: Run an UPDATE query with no WHERE clause and change all [Phone#] to "(" & MID([Phone#],1,3) & ") " & MID([Phone#],4,3) & "-" & MID([Phone#],7)
 

Above assumes table field is [Phone#], and your area code is 303. Change as necessary.
It took me less time to do the project than it did to type this message. Should take about 15 minutes, total.