Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Data Cleansing - Part 2

Posted on 2014-03-11
Medium Priority
201 Views
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
Question by:Patrick O'Dea
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 2

LVL 35

Accepted Solution

Dan Craciun earned 2000 total points
ID: 39922215

HTH,
Dan
SerialNumbersPuzzle2.xlsm
0

Author Closing Comment

ID: 39922223
Perfect!
0

LVL 35

Expert Comment

ID: 39922233
0

LVL 19

Expert Comment

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 35

Expert Comment

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 35

Expert Comment

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

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 35

Expert Comment

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

## Featured Post

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month9 days, 16 hours left to enroll