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
8
Medium Priority
?
201 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
[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
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 2000 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 35

Expert Comment

by:Dan Craciun
ID: 39922233
Glad I could help!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 35

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 35

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 35

Expert Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…

609 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