Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In honesty, I have absolutely no understanding of this code, other than it looks at the text string in column 30, and returns a new text string of 16 numbers (removing spaces) in column 31.

    This is code that I inherited and if my memory serves me correctly, Hans assisted me to adapt it slightly, some time ago.

    [codebox]
    Public Sub GetNumber3()

    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("Walker").UsedRange.Columns(30).Cells

    sVal = Replace(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, 1).Value = sPart
    Exit For
    Next
    Set sParts = Nothing

    Next

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

    End Sub
    [/codebox]

    I have 2 questions:

    Firstly, can this be adapted to pull the 16 digit string only from the first occurrence of the numbers 4 or 5. In other words, if the string was "Pls DR 030809 4474 6285 5279 6632", then "4474628552796632" would be returned.

    Secondly, would this same task be achievable using a worksheet formula?

    Thanks in advance

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

    rPart.Pattern = "\d{16}"

    to

    rPart.Pattern = "[45]\d{15}"

    Explanation:

    \d means: any digit (0...9).
    {16} means: exactly 16 occurrences of the previous item.
    So \d{16} means: exactly 16 digits.

    [45] means: either 4 or 5.
    \d{15} means: exactly 15 digits.
    So [45]\d{15} means: a 4 or 5 followed by 15 digits.

    You could define a custom function:

    Code:
    Public Function Get16Digits(strVal) As String
      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
      sVal = Replace(strVal, " ", "")
      'Set matches, using regular expressions to contain the numeric chars
      rPart.Pattern = "[45]\d{15}"
      'get the results
      Set sParts = rPart.Execute(sVal)
      'get the first match
      For Each sPart In sParts
    	Get16Digits = sParts(0)
    	Exit For
      Next sPart
      Set sParts = Nothing
    End Function
    and use it like this in a cell formula:

    =Get16Digits(B23)

    where B23 is a cell containing the string you want to extract 16 digits from. It'll be slow, though!

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, I'll use the quick fix for now and explore the possibility of using the function later.

Posting Permissions

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