R. Choate,

The next routine finds the first "string" of 10 numbers inside a string,
beginning at the left side from the string.
Hope this is what you want!

Greetz,
Rody

Sub extract_ten_digits_number_from_string()
'' 27-10-2005 RM
MyValue = Sheets("blad1").Range("a1").Value
Dim counter1 As Long ''(remaining ?) digits inside of the string
counter1 = Len(MyValue)
teller = 1 '' place of digit inside the string
If counter1 < 10 Then Exit Sub '' less then 10 digits, please leave a.s.a.p!
start:
If IsNumeric(Left(MyValue, teller)) Then
If teller 10 Then GoTo finish '' Yes we've got a string of 10
digits (it's a number, i know.....)
teller = teller + 1
GoTo start
Else
MyValue = Right(MyValue, counter1 - teller)
counter1 = Len(MyValue)
If Len(MyValue) < 10 Then
MsgBox "No string of 10 digits detected"
Exit Sub
End If
teller = 1
GoTo start
End If
finish:
MsgBox "Yes.......... " & Left(MyValue, 10)
End Sub





"R. Choate" schreef in bericht
...
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