+ Reply to Thread
Results 1 to 21 of 21

Extract 10 digit number from string

  1. #1
    Toppers
    Guest

    RE: Extract 10 digit number from string

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


  2. #2
    R. Choate
    Guest

    Extract 10 digit number from string

    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




  3. #3
    R. Choate
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message news:[email protected]...
    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
    >
    >
    >
    >




  4. #4
    Eric White
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message news:[email protected]...
    > 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
    > >
    > >
    > >
    > >

    >
    >
    >


  5. #5
    Toppers
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message news:[email protected]...
    > 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
    > >
    > >
    > >
    > >

    >
    >
    >


  6. #6
    Rody Meulman
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >>
    >>
    >>
    >>

    >
    >




  7. #7
    R. Choate
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message news:[email protected]...
    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" <[email protected]> wrote in message news:[email protected]...
    > 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
    > >
    > >
    > >
    > >

    >
    >
    >




  8. #8
    John Coleman
    Guest

    Re: Extract 10 digit number from string


    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" <[email protected]> wrote in message news:[email protected]...
    > 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


  9. #9
    Toppers
    Guest

    Re: 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" <[email protected]> wrote in message news:[email protected]...
    > > 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
    > > >
    > > >
    > > >
    > > >

    > >
    > >
    > >


  10. #10
    Toppers
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message news:[email protected]...
    > 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" <[email protected]> wrote in message news:[email protected]...
    > > 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
    > > >
    > > >
    > > >
    > > >

    > >
    > >
    > >

    >
    >
    >


  11. #11
    Bob Phillips
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message

    news:[email protected]...
    > 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
    > >
    > >
    > >
    > >

    >
    >




  12. #12
    Peter T
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >
    >
    >




  13. #13
    R. Choate
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message news:[email protected]...
    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message

    news:[email protected]...
    > 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
    > >
    > >
    > >
    > >

    >
    >





  14. #14
    John Coleman
    Guest

    Re: Extract 10 digit number from string


    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" <[email protected]> wrote in message news:[email protected]...
    > > 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


  15. #15
    Dave Peterson
    Guest

    Re: 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" <[email protected]> wrote in message news:[email protected]...
    > > > 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

  16. #16
    R. Choate
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message news:[email protected]...

    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" <[email protected]> wrote in message news:[email protected]...
    > > 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



  17. #17
    R. Choate
    Guest

    Re: Extract 10 digit number from string

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

    Richard

    --
    RMC,CPA


    "Dave Peterson" <[email protected]> wrote in message news:[email protected]...
    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" <[email protected]> wrote in message news:[email protected]...
    > > > 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



  18. #18
    John Coleman
    Guest

    Re: Extract 10 digit number from string


    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" <[email protected]> wrote in message news:[email protected]...
    >
    > 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" <[email protected]> wrote in message news:[email protected]...
    > > > 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



  19. #19
    R. Choate
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message news:[email protected]...
    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





  20. #20
    Dave Peterson
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message news:[email protected]...
    >
    > 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" <[email protected]> wrote in message news:[email protected]...
    > > > 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

  21. #21
    R. Choate
    Guest

    Re: Extract 10 digit number from string

    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" <[email protected]> wrote in message news:[email protected]...
    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" <[email protected]> wrote in message news:[email protected]...
    >
    > 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" <[email protected]> wrote in message news:[email protected]...
    > > > 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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1