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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Akuini

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

Watch MrExcel Video

Forum statistics

Threads
1,089,900
Messages
5,411,104
Members
403,341
Latest member
April2020

This Week's Hot Topics

Top