ExcelBanter - View Single Post - Formula to extract digits from a text string?

On Fri, 13 Jan 2006 21:39:50 -0500, wrote:

Gang -

Dealing with a dataset of phone numbers. Engineers have figured out
about 18 different ways, using any combinations of spaces, parens,
dashes, periods and digits to enter their phone numbers.

I know I can write a UDF that will substitute out all the alpha
characters, one by tedious one. I was wondering if there was a clever
formula that will pull the numerics out, in the sequence entered. In
other words, for (123) 456-7890, 1234567890 as text results. Similarly
for 123.456.7890 and 123-456-7890 and 123 456 7890, and especially
123456789 or 123.4567890, which Excel insists are numbers etc.

Thanks in advance.
...best, Hash

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

--ron