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

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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]





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
 
Upvote 0
You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top