VBA Scan Data in J and find Required numbers

Darth269

New Member
Joined
Oct 4, 2018
Messages
15
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).
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,485
Office Version
365
Platform
Windows
Using vba, try this:
I put the result in col K

Code:
[FONT=lucida console][color=Royalblue]Sub[/color] a1111685a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1111685-vba-scan-data-j-find-required-numbers.html[/color][/i]
[color=Royalblue]Dim[/color] i [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] va, vb
[color=Royalblue]Dim[/color] regEx [color=Royalblue]As[/color] [color=Royalblue]Object[/color]

va = Range([color=brown]"J2"[/color], Cells(Rows.count, [color=brown]"J"[/color]).[color=Royalblue]End[/color](xlUp))
[color=Royalblue]ReDim[/color] vb([color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color]), [color=crimson]1[/color] [color=Royalblue]To[/color] [color=crimson]1[/color])
       
        [color=Royalblue]Set[/color] regEx = CreateObject([color=brown]"VBScript.RegExp"[/color])
        [color=Royalblue]With[/color] regEx
            .[color=Royalblue]Global[/color] = [color=Royalblue]True[/color]
            .MultiLine = [color=Royalblue]True[/color]
            .IgnoreCase = [color=Royalblue]True[/color]
            .pattern = [color=brown]"(\d{7,})"[/color]
        [color=Royalblue]End[/color] [color=Royalblue]With[/color]

    [color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
        
        [color=Royalblue]If[/color] regEx.test(va(i, [color=crimson]1[/color])) [color=Royalblue]Then[/color]
            [color=Royalblue]Set[/color] matches = regEx.Execute(va(i, [color=crimson]1[/color]))
            vb(i, [color=crimson]1[/color]) = matches([color=crimson]0[/color]).SubMatches([color=crimson]0[/color])
        [color=Royalblue]End[/color] [color=Royalblue]If[/color]
    
    [color=Royalblue]Next[/color]

[i][color=seagreen]'Put the result in col K[/color][/i]
Range([color=brown]"K2"[/color]).Resize(UBound(vb, [color=crimson]1[/color]), [color=crimson]1[/color]) = vb

[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]



<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(222,252,236)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(222,252,236);text-align: center;color: rgb(22,17,32)"><th></th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">7258243</td><td style="text-align: right;;">7258243</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">7255953</td><td style="text-align: right;;">7255953</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">7258152</td><td style="text-align: right;;">7258152</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">000000007257293, Mars</td><td style="text-align: right;;">7257293</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">000000007257518,</td><td style="text-align: right;;">7257518</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">000000007257286,</td><td style="text-align: right;;">7257286</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">NA</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">000000007256943,</td><td style="text-align: right;;">7256943</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">there was no ticket number</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">forgotten</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">000000007256524, SRBUK - Password Unlock</td><td style="text-align: right;;">7256524</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(222,252,236);color: rgb(22,17,32)">Sheet1</p><br /><br />
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,485
Office Version
365
Platform
Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Forum statistics

Threads
1,086,020
Messages
5,387,267
Members
402,054
Latest member
JHENGNDK

Some videos you may like

This Week's Hot Topics

Top