Results 1 to 6 of 6

Thread: VBA Scan Data in J and find Required numbers

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Scan Data in J and find Required numbers

    So, i have a lot of data in a table, this is from Surveys sent out.
    A lot of customers don't give the right ticket number, making the data useless, or dont give it in the format i need making it time consuming to correct by hand. I have no control over the survey itself, so cant put limits on that, so I was wondering if it's possible to scan through Column J, and correct, or remove the data?

    Below is a sample of the data we might see... In the example, the first 3 entries are correct, everything else is invalid and either needs to be removed (as in row 7), or amended, like 4,5,& 6.


    7258243
    7255953
    7258152
    000000007257293, Mars
    000000007257518,
    000000007257286,
    NA
    000000007256943,
    there was no ticket number
    forgotten
    000000007256524, SRBUK - Password Unlock



    Is there a way to loop through J and provide 1 of 3 outcomes for each entry...


    • Leave it alone if a 7 digit number exists (Not starting with a 0)
    • Remove characters before and after the 7 digit number (0's and text)
    • Remove the line entirely?


    Hope this is enough information for people to point me in the right direction. For the foreseeable future, the numbers will start with a 7, 8 or 9, but there is no way to guarantee this, if we change our support tool for example (which is on the cards).

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,148
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA Scan Data in J and find Required numbers

    Using vba, try this:
    I put the result in col K

    Code:
    Sub a1111685a()
    'https://www.mrexcel.com/forum/excel-questions/1111685-vba-scan-data-j-find-required-numbers.html
    Dim i As Long
    Dim va, vb
    Dim regEx As Object
    
    va = Range("J2", Cells(Rows.count, "J").End(xlUp))
    ReDim vb(1 To UBound(va, 1), 1 To 1)
           
            Set regEx = CreateObject("VBScript.RegExp")
            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = True
                .pattern = "(\d{7,})"
            End With
    
        For i = 1 To UBound(va, 1)
            
            If regEx.test(va(i, 1)) Then
                Set matches = regEx.Execute(va(i, 1))
                vb(i, 1) = matches(0).SubMatches(0)
            End If
        
        Next
    
    'Put the result in col K
    Range("K2").Resize(UBound(vb, 1), 1) = vb
    
    End Sub



    Excel 2013
    JK
    1
    272582437258243
    372559537255953
    472581527258152
    5000000007257293, Mars7257293
    6000000007257518,7257518
    7000000007257286,7257286
    8NA
    9000000007256943,7256943
    10there was no ticket number
    11forgotten
    12000000007256524, SRBUK - Password Unlock7256524

    Sheet1




  3. #3
    New Member
    Join Date
    Oct 2018
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Scan Data in J and find Required numbers

    Oh seriously! You've saved me so much time you have no idea! Thank you!

  4. #4
    New Member
    Join Date
    Oct 2018
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Scan Data in J and find Required numbers

    I can't seem to edit my post to show resolved... Can anyone advise?

  5. #5
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,148
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA Scan Data in J and find Required numbers

    You're welcome, glad to help, & thanks for the feedback.

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,148
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA Scan Data in J and find Required numbers

    Quote Originally Posted by Darth269 View Post
    I can't seem to edit my post to show resolved... Can anyone advise?
    You can't do that in this forum.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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