Phone number insanity

[Dragonflight]

I'm working on a thousand-line database, and we need to streamline the data to a uniform format. The data was provided from all over the place, so the phone number data is formatted in just about every way there is. For example:

(123) 456-7890 <--- this is the desired result
1-234-567-8900
123-4567
123-456-7890
123- 456-7890
123 456 7890

The thought of having to go through this list manually horrifies me. It's got two phone numbers, cell, pager, fax, and alternate contact numbers, all done up in this jumble. Manual formatting would drive me insane. Is there a way to fix this problem faster?

 

[Joe Was]

Added a little error trap:

Sub ConvertPhoneNumbers()
'Sheet module code, like: Sheet1.
Dim c As Range
Dim myLen, myBad$

With Range([A1], [A65536].End(xlUp))
.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="(", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With

For Each c In Range([A1], [A65536].End(xlUp))
myLen = Len(c)
If myLen = 11 Then
c = Right(c, 10)
myLen = 10
End If
If myLen = 10 Then
c.NumberFormat = "(###) ###-####"
End If
If myLen = 7 Then
c.NumberFormat = "###-####"
End If
If myLen > 13 Or myLen = 12 Or myLen = 9 Or myLen = 8 Or myLen < 7 Then
myBadNum = c.Value & " Bad#?"
c.Value = myBadNum
End If

Next c
End Sub