Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and extract string of 16 digits (2002)

    I have inherited the code below which extracts a 16 digit numeric string from a variable string where the 16 digits can be anywhere within. It works, however, sometimes the 16 digit string may contain one, two or even three spaces, at which point, the extract fails. Would there be a way to 'fix' this? Maybe a second loop to extract the first 16 numeric digits found, if the first loop failed?

    Option Explicit

    Public Sub GetNumber()

    Dim sParts
    Dim sPart
    Dim c As Range
    Dim cRow As Long
    Dim sVal As String

    'Set the late binding objects
    Dim rPart As Object

    Set rPart = CreateObject("VBScript.RegExp")
    rPart.Global = True
    rPart.IgnoreCase = True

    For Each c In Sheets("GET NUMBER").UsedRange.Columns(4).Cells

    sVal = c.value
    cRow = c.Row

    'Set matches, using regular expressions to contain the numeric chars
    rPart.Pattern = "d{16}"
    'get the results
    Set sParts = rPart.Execute(sVal)
    'get the first match
    For Each sPart In sParts
    'the character part is equal to the total string - the lenght of the numeric part
    c.Offset(0, 5).value = sPart
    Exit For
    Next
    Set sParts = Nothing

    Next

    Set sParts = Nothing
    Set sPart = Nothing
    Set rPart = Nothing

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and extract string of 16 digits (2002)

    If there's spaces in between, it's not 16 digits, innit? Please explain more clearly what you want.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and extract string of 16 digits (2002)

    See attached:
    The red cells are not populated due to spaces. Could another loop extract the first available 16 numerics that are found, ignoring spaces??

    Thanks
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and extract string of 16 digits (2002)

    Change the line
    <code>
    sVal = c.Value
    </code>
    to
    <code>
    sVal = Replace(c.Value, " ", "")</code>

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and extract string of 16 digits (2002)

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> I'm lost for words......

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find and extract string of 16 digits (2002)

    Firstly, I know the OP is looking for VBA solution and HansV already solved the problem

    However, in count of the VBA solution, I find that using 612 characters

    The following Formula solution , just posted for your information only,

    The 2 Formula solution, counted in use 70 and 60 characters respectively, only around 10% of the VBA solution

    1] =MID(TRIM(D9),MIN(FIND({0,1,2,3,4,5,6,7,8,9},TRIM( D9)&1234567890)),16)

    2] {=MID(TRIM(D9),MIN((FIND(ROW($1:$10)-1,TRIM(D9)&5^19))),16)}

    Regards
    Bosco

Posting Permissions

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