Solved

Data Cleansing - Part 2

Posted on 2014-03-11
8
188 Views
Last Modified: 2014-03-12
Hi,

This is a follow up to an earlier question (and solution).

See attached.
I was provided with a function getpartnum which found any 4 (or more) consecutive digits in any one cell. Note the result of this in column "D" of my sheet.

I need to extend the logic to find a second series of 4 (or more) consecutive digits.

So for example,
S/n12344  PP12314   - This should find answers in 2 separate cells - 12344 12314
See examples in column "E".


The existing function simply finds the FIRST occurrence of 4 or much digits.
How do I find a second series of 4 (or more) digits.

The first and second series MUST be separated by any character other than 0-9.
SerialNumbersPuzzle.xlsm
0
Comment
Question by:Patrick O'Dea
  • 5
  • 2
8 Comments
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39922215
Please find attached the solution.

HTH,
Dan
SerialNumbersPuzzle2.xlsm
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 39922223
Perfect!
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39922233
Glad I could help!
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39922301
Dan  beat me to it (again :) but as my solution is, imho, more elegant I'll add it anyway,

use the second parameter of gerpartnum to extract the n'th occurrence of a 4-9 digit number

so getpartnum(b2,0) gets the first, getpartnum(b2,1) gets the second and so on
Copy-of-SerialNumbersPuzzle.xlsm
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39922742
regmigrant: Yup, your solution is more elegant. IMO also :)

I'm not an VBA developer, but I saw something that could be solved with regular expressions and I just had to post a solution before the heavyweights of Excel came up with 1000 char ugly hacks with FIND/MATCH/INDEX.


Dan
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39922812
BTW, I found this nice article from Patrick Mathews: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

So, instead of reinventing the wheel, we could of just copied his code and then used
=RegExpFind(B2, "\d{4,9}",1)
and change the 1 for 2, 3 etc when needed the next match.

The file is attached.

HTH,
Dan
SerialNumbersPuzzle-withRegex.xlsm
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39922843
dan- "1000 char ugly hacks" should be a trope for the site, we just need a cool name - 1kChUhk ?? - as in, "that works but its a 1kchuck" :)

- the Patrick solution is neat and I struggled to stop a return where n>0 and only one match occurs, hence the clunky, "If - elseif" combo in my version- I thought someone with a better understanding of Regex might be able to handle it better but his version seems to solve that in a similar way whilst adding in a lot of useful extra stuff, definitely worth keeping
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39923011
1k chuck... has a nice ring to it :)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now