Maybe instead of checking if the 10 character string is numeric, it would be
better to just check to see if those 10 characters are digits:

Option Explicit
Function Extract10Digits(myStr As String) As String

Dim iCtr As Long
Dim myOutStr As String

myOutStr = "Not Found"
For iCtr = 1 To Len(myStr)
If Mid(myStr, iCtr, 10) Like String(10, "#") Then
'found it
myOutStr = Mid(myStr, iCtr, 10)
Exit For
End If
Next iCtr

Extract10Digits = myOutStr

End Function



Toppers wrote:

Eric,
I tried your function (looked better than my solution!) but with
the following string I got an answer of "+123456789" rather than "1234567890"

ans = Extract10("*/abcDEF+1234567890zt1")

Without the "+" I got 1234567890.

Equally a "-" also gives "-123456789"

"Eric White" wrote:

Try this:

Function Extract10(strCellValue as String) as String

Dim x as Long

x = 0

Do
x = x + 1
If IsNumeric(Mid(strCellValue, x, 10)) Then
Extract10 = Mid(strCellValue, x, 10)
Exit Do
End If
Loop Until x + 10 = Len(strCellValue)

End Sub

"R. Choate" wrote:

They are all over the place. A stupid data entry person enters everything randomly. The numbers could be anywhere in the string,
otherwise I would just look for the delimiter. That would be easy. The only constant is that it is always 10 digits...somewhere in
the string.
--
RMC,CPA


"Toppers" wrote in message ...
Richard,
How are the (numeric) strings delimited e.g. 1234,abc,123456?

"R. Choate" wrote:

I need to find and extract a 10 digit number from a string which might contain more than one numeric string, but there would only
be
one string of that length. I'm going to have to find out how to do this and then loop through a long list and extract that number
and place it in the first cell to the right (column B). I've been at this all morning. I'm out of ideas. Surely there is an easy
way
to do this that I'm just not thinking of. Help !

Thanks in advance !

Richard
--
RMC,CPA








--

Dave Peterson