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

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,563
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,563
Office Version
365
Platform
Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,090,243
Messages
5,413,280
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top