Extract 10 digit number from string

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

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