Solved

Data Cleansing - Part 2

Posted on 2014-03-11
8
193 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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