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