VBA Scan Data in J and find Required numbers

[Darth269]

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]

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

[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]

Excel 2013
	J	K
1
2	7258243	7258243
3	7255953	7255953
4	7258152	7258152
5	000000007257293, Mars	7257293
6	000000007257518,	7257518
7	000000007257286,	7257286
8	NA
9	000000007256943,	7256943
10	there was no ticket number
11	forgotten
12	000000007256524, SRBUK - Password Unlock	7256524
Sheet1

 

[Darth269]

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

 

[Darth269]

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

 

[Akuini]

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

 

[Akuini]

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

You can't do that in this forum.