This is pure VB. It returns a 7 or 10 digit result, trimming of leading
characters. So if string "12345678901" includes the long distance enum,
it trims off the leading '1' and returns "(234) 567-8901".

Change the formats to suit your needs...


Function FormatPhoneNumber$(TextIn$)
' Strips out any embeded formatting characters and inserts dashes,
spaces and '()' in the correct positions.
' Handles 7-digit and 10-digit phone numbers. Allows extensions.
' Arguments: TextIn The string being filtered
' Returns: Properly formatted phone number as a string
' Requires the FilterString() function.

Const sSource As String = "FormatPhoneNumber()"

If TextIn = "" Then Exit Function
Dim sTmp$, sExt$, iPos%, bHasExt As Boolean

'Check for an extension
iPos = InStr(1, TextIn, Choose(3, "Ext", "EXT", "ext"),
vbTextCompare)
If iPos 0 Then
bHasExt = True
sExt = FilterString(Mid(TextIn, iPos), , False)
TextIn = Left(TextIn, iPos - 1)
End If

' Get rid of any unwanted characters
sTmp = FilterString(TextIn, , False)
' Insert dashes in the correct positions
If Len(sTmp) <= 7 Then
If bHasExt Then
FormatPhoneNumber = Format$(sTmp, "!@@@-@@@@") & " Ext " & sExt
Else
FormatPhoneNumber = Format$(sTmp, "!@@@-@@@@")
End If
Else
If bHasExt Then
FormatPhoneNumber = Format$(sTmp, "!(@@@) @@@-@@@@") _
& " Ext " & sExt
Else
FormatPhoneNumber = Format$(sTmp, "!(@@@) @@@-@@@@")
End If
End If

End Function 'FormatPhoneNumber()

Function FilterString$(ByVal TextIn$, Optional IncludeChars$, _
Optional IncludeLetters As Boolean = True, _
Optional IncludeNumbers As Boolean = True)
' Filters out all unwanted characters in a string.
' Arguments: TextIn The string being filtered.
' IncludeChars [Optional] Any non alpha-numeric
characters to keep.
' IncludeLetters [Optional] Keeps any letters.
' IncludeNumbers [Optional] Keeps any numbers.
'
' Returns: String containing only wanted characters.
' Comments: Works very fast using the Mid$() function over other
methods.

Const sSource As String = "FilterString()"

'The basic characters to always keep by default
Const sLetters As String = "abcdefghijklmnopqrstuvwxyz"
Const sNumbers As String = "0123456789"

Dim i&, CharsToKeep$

CharsToKeep = IncludeChars
If IncludeLetters Then _
CharsToKeep = CharsToKeep & sLetters & UCase(sLetters)
If IncludeNumbers Then CharsToKeep = CharsToKeep & sNumbers

For i = 1 To Len(TextIn)
If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then _
FilterString = FilterString & Mid$(TextIn, i, 1)
Next
End Function 'FilterString()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion