Hi Dave,
Thanks again. Also, thanks to John for his code which also works after I check the reference to the VBscript Regular Expressions.
Richard
--
RMC,CPA


"Dave Peterson" wrote in message ...
Did you do this portion:

To use it you would need to include a reference to Microsoft VBScript
Regular Expressions 5.5 to your project (tools-reference in the VBA
editor).

Inside the VBE, select your code, then tools|references and scroll down that
list.

"R. Choate" wrote:

Hi John,

I get an error on your code because this apparently isn't in my library. Says "user defined type not defined" and it highlights
your
line "Dim RE As New RegExp"

Any suggestions?

--
RMC,CPA

"John Coleman" wrote in message oups.com...

John Coleman wrote:
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


Sounds like a job for Regular Expressions:

Function Extract(S As String) As String
Dim RE As New RegExp
Dim MyMatches As MatchCollection
Dim MyMatch As Match

RE.Pattern = "(?:^|[^0-9])([[0-9]{10})(?:[^0-9]|$)"
Set MyMatches = RE.Execute(S)
If MyMatches.Count = 0 Then
Extract = ""
Else
Set MyMatch = MyMatches(0)
Extract = MyMatch.SubMatches(0)
End If

End Function

This function takes a string which contains a 10 digit number and
returns the first such number (returns it as a string - you could
convert to a number if need just assign it to a variant and then treat
the variant as a number should implicitly cast, with 10 digits you
might have overflow with Long.) It won't return the first 10 digits of
a 15 digit number (say) and seems to work if the number is flush
against either end of the string. It returns the empty string in the
event of no such match. I don't know exactly what your strings look
like so you would need to test the above. For example, you would need
to modify it to accept + or - signs if you need to.

To use it you would need to include a reference to Microsoft VBScript
Regular Expressions 5.5 to your project (tools-reference in the VBA
editor).

Hope that helps

-John Coleman

Somewhat strangely, it seems that a stray [ crept into my code(even
more strangley, the code seems to work nevertheless). In any event, it
should have been:

Function Extract(S As String) As String
Dim RE As New RegExp
Dim MyMatches As MatchCollection
Dim MyMatch As Match

RE.Pattern = "(?:^|[^0-9])([0-9]{10})(?:[^0-9]|$)"
Set MyMatches = RE.Execute(S)
If MyMatches.Count = 0 Then
Extract = ""
Else
Set MyMatch = MyMatches(0)
Extract = MyMatch.SubMatches(0)
End If

End Function

Sorry for any confusion

-John Coleman

--

Dave Peterson