"Ron Rosenfeld" wrote...
....
I would use a UDF. You do have to go through each character, but the code
is
pretty short.

=============================
Function PN(PhonNum) As Double
Dim i As Long
Dim a
Dim temp As String

For i = 1 To Len(PhonNum)
a = Mid(PhonNum, i, 1)
If a Like "[0-9]" Then
temp = temp & a
End If
Next i

If Len(temp) 1 Then
PN = Left(temp & "00000000", 10)
Else
PN = "" 'Gives #VALUE error if no PhonNum
End If

End Function
==========================

I thought you had become a regular expression advocate.

An alternative would be to use the REGEX.SUBSTITUTE function in Laurent
Longre's MOREFUNC.XLL add-in, available at

http://xcell05.free.fr/english

and use it as

=REGEX.SUBSTITUTE(A1,"\D+")

With regard to your udf, arguable whether you should pad the result with
zeros. Better to just return the digits found, especially if some of the
clever users entering phone numbers enter something like 1-234-555-1212.
Also, my reading of the OP was that the results should be text. If so, bad
to set the return type to double rather than string. And, FTHOI, I'd write
it as


Function foo(p As Variant) As String
Dim t As String, n As Long

t = CStr(p)
n = Len(t)

Do While n 0
If Not Mid$(t, n, 1) Like "#" Then Mid$(t, n, 1) = " "
n = n - 1
Loop

'assumes VBA6; use Application.WorksheetFunction.Substitute w/VBA5
foo = Replace(t, " ", "")
End Function