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

On Sat, 14 Jan 2006 10:28:27 -0500, wrote:

Ron -

Thank you. That's much simpler than the UDF I would have written. I
think PN is text at the bottom and a double at the top?

Yeah, but it gets outputted as a Double -- as Harlan points out, and I agree,
it should probably be a String. I had left it as a number so you could use a
custom format for display, but you could just as easily format it within the
UDF.

Also, you might want to add some logic to test for legitimate data. Obviously
10 digits should be legit; 9 might be missing a trailing zero, if the data is
numeric; 11 might have a leading '1'; etc. Or perhaps you could use data
validation to force correct entry.

Glad to help.

Never-the-less, much simpler. Thanks.

...best, Hash

In article ,
Ron Rosenfeld wrote:

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

--ron