Extract 10 digit number from string

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" <Toppers@discussions.microsoft.com> wrote in message news:02E8F4D4-4361-4505-AE87-7B7E23A120BD@microsoft.com...
> > > 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

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

Richard

--
RMC,CPA


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:436141E8.7B979CB4@verizonXSPAM.net...
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" <Toppers@discussions.microsoft.com> wrote in message news:02E8F4D4-4361-4505-AE87-7B7E23A120BD@microsoft.com...
> > > 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