# Data Cleansing - Part 2

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
###### Who is Participating?

IT ConsultantCommented:

HTH,
Dan
SerialNumbersPuzzle2.xlsm
0

Author Commented:
Perfect!
0

IT ConsultantCommented:
0

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

IT ConsultantCommented:
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

IT ConsultantCommented:
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

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

IT ConsultantCommented:
1k chuck... has a nice ring to it :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.