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

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

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

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

Hi,

See if this helps:


Sub Test()
Call FindTenDigits("1abc1234defghi1234567890zt1")
End Sub


Sub FindTenDigits(FindStr)

For i = 1 To Len(FindStr)

If IsNumeric(Mid(FindStr, i, 1)) Then
For j = i To Len(FindStr)
If Not IsNumeric(Mid(FindStr, j, 1)) Then
If j - i = 10 Then
NumStr = Mid(FindStr, i, 10)
MsgBox NumStr & " found in string " & FindStr
Exit Sub
End If
i = j
Exit For
End If
Next j
End If

Next i

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

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

Well, I'm getting the understanding that so far, people feel that I'm just going to have to loop through every string while I loop
through every row (thousands of rows). I would really love to avoid looping through every character until I find 10 digits if that
is possible. I can't believe there isn't a built-in method for this. That is going to put a real time eater in my app.

If anybody can think of a way to do this without going through each string, character by character, please tell me. For those who
have given me code which does loop through, I thank you for your time and your help.
--
RMC,CPA


"Toppers" wrote in message ...
Hi,

See if this helps:


Sub Test()
Call FindTenDigits("1abc1234defghi1234567890zt1")
End Sub


Sub FindTenDigits(FindStr)

For i = 1 To Len(FindStr)

If IsNumeric(Mid(FindStr, i, 1)) Then
For j = i To Len(FindStr)
If Not IsNumeric(Mid(FindStr, j, 1)) Then
If j - i = 10 Then
NumStr = Mid(FindStr, i, 10)
MsgBox NumStr & " found in string " & FindStr
Exit Sub
End If
i = j
Exit For
End If
Next j
End If

Next i

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

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

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

Richard,
Assuming we don't find a magic solution, you could speed
things up considerably by reading data into an array and processing "in
memory" as opposedto reading row by row/cell by cell.



"R. Choate" wrote:

Well, I'm getting the understanding that so far, people feel that I'm just going to have to loop through every string while I loop
through every row (thousands of rows). I would really love to avoid looping through every character until I find 10 digits if that
is possible. I can't believe there isn't a built-in method for this. That is going to put a real time eater in my app.

If anybody can think of a way to do this without going through each string, character by character, please tell me. For those who
have given me code which does loop through, I thank you for your time and your help.
--
RMC,CPA


"Toppers" wrote in message ...
Hi,

See if this helps:


Sub Test()
Call FindTenDigits("1abc1234defghi1234567890zt1")
End Sub


Sub FindTenDigits(FindStr)

For i = 1 To Len(FindStr)

If IsNumeric(Mid(FindStr, i, 1)) Then
For j = i To Len(FindStr)
If Not IsNumeric(Mid(FindStr, j, 1)) Then
If j - i = 10 Then
NumStr = Mid(FindStr, i, 10)
MsgBox NumStr & " found in string " & FindStr
Exit Sub
End If
i = j
Exit For
End If
Next j
End If

Next i

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

Surely, the answer is to educate the data inputters to enter the data
properly, otherwise you are chasing a moving target. May times it should be
procedure not process.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"R. Choate" wrote in message
...
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

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

Hi Bob,

Boy would I love to educate this user! This person is, somehow, the controller of the company (small company). But, since she isn't
a CPA, she signs her name, including in her email signature, as "Mary Dumbass, MBA". She just has to have some initials behind her
name, even if she can't use the ones she wants. There is no way I can get this user to "behave". She is incompetent and in over her
head, but she is too stupid to know she is in over her head. She doesn't even understand why she should enter data in a consistent
manner. Now they are paying part of the price.

I do like the array idea. I should do that until a better way comes along. Right now, I'm trying to make Eric's solution work. I'm
doing something wrong because it isn't returning an answer. My string almost always starts with regular text and the numeric portion
is either in the middle or towards the end. You should see what I had to loop through and clean up before even getting to this part.
Geez!
--
RMC,CPA


"Bob Phillips" wrote in message ...
Surely, the answer is to educate the data inputters to enter the data
properly, otherwise you are chasing a moving target. May times it should be
procedure not process.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"R. Choate" wrote in message
...
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

Hi Richard,

You've had loads of ideas already, another just for luck.

This should extract only digits amongst other characters and check the
length

Sub test()
Dim bArr() As Byte

Dim vIn

vIn = Application.InputBox("Enter 10 digit number")
bArr = StrConv(vIn, vbFromUnicode)
For i = 0 To UBound(bArr)
Select Case bArr(i)
Case 48 To 57
Case Else
bArr(i) = 32
End Select
Next

vIn = StrConv(bArr, vbUnicode)

' Replace n/a in xl 97, use Application.Substitute
vIn = Replace(vIn, " ", "")

MsgBox vIn & vbCr & _
IIf(Len(vIn) = 10, "OK", "Bad input person")

End Sub

Wouldn't take much to adapt to find the first consecutive 10 digit string if
that's what's required (vs 10 digits anywhere).

Regards,
Peter T

"R. Choate" wrote in message
...
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

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

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

PERFECT !! That was it ! Thank you VERY, VERY much !!

Richard

--
RMC,CPA


"Dave Peterson" wrote in message ...
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

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?



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).

-John

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

Thanks everybody, I appreciate all of the ideas. Dave Peterson tweaked the suggestions from Eric and from Toppers and came up with a
function that worked great on the 1st shot. I think this will get me to where I need to be.

Richard

--
RMC,CPA


"R. Choate" wrote in message ...
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

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

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