Excel and extrapulating data

In Excel how can I take S1-S5 or C7-C10 (letter or number can vary) and have excel parse it out to S1,S2,S3,S4,S5 or C7,C8,C9,C10
Rachelle ThorenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Are you looking to do this in an Excel formula on a sheet, or in a VBA macro procedure?

Can you expand a little bit more about how you will be using this, it might help guide the best approach to a solution.


»bp
0
Rachelle ThorenAuthor Commented:
Sure.  I have a parts listing document that when created does not spell out every single part but rather will give a list of parts with some dashes to save spaces.  For example, I'll have 200 parts S1,S3, S29, S30-S70, S75-S80 etc.  I have customers who need me to list every single part by S1,S3,S29,S30,S31,S32 etc.  They need every single part listed individually.  I would love it if Excel could extract that data rather than me going back into the data on 1,000 of part orders to change the data to meet their request.  Does that make sense?
0
Bill PrewCommented:
Okay, that helps some.  So the basic text to be expanded will always be of a format XXXXaaaa-XXXXbbbb where XXXX could be one or more characters (never numbers) of a "part number range" and then aaaa and bbbb will be the starting and ending range for the suffix of each part number?

Will this range string be a free standing column in Excel, or is it embeded in a longer string (your reply seemed to imply that it could be something like "S1,S3, S29, S30-S70, S75-S80" but perhaps I misunderstood.

Do you already have an Excel sheet with some sample / test data in it, and if so could you upload it here, that would make things easier to understand as well.

In Excel it could be possible to generate the "extrapolation".  Would the result be a single string of all the individual part numbers comma delimited, or would they be placed on separate rows, one part number per row or something?  And, this is where an example sheet can help a lot to visualize what you are planning.


»bp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rachelle ThorenAuthor Commented:
The information would be a string within a cell.  I would like it to be added within the cell where the information is listed.
0
Bill PrewCommented:
So a few questions left that need to be answered please.  Sorry to be so detail oriented, but it really saves time if we know exactly what is needed before trying to work up a code solution.

  1. Will the basic text to be expanded always be of a format XXXXaaaa-XXXXbbbb where XXXX could be one or more characters (letters, never numbers, hyphen or special characters) of a "part number range" and then aaaa and bbbb will be the starting and ending range for the suffix of each part number?

  2. In the part number cells to be adjusted, will this range string be the only thing in the cell, or is it embeded in a longer string like  "S1,S3, S29, S30-S70, S75-S80"?

  3. It sounds like you want a VBA macro that when executed processes all the part numbers in a particular column and expands the ranges updating the cells with the expanded results, correct?


»bp
0
Rachelle ThorenAuthor Commented:
1.  That is correct.  Text will always be one alphanumerical character followed by a numerical integer.  It increases in number but the letter never changes within the cell.
2.  It will be embedded into a longer string.
3.  Yes.

Thank you!!!
0
Bill PrewCommented:
Running short on time but wanted to get this to you for some testing there.  Attached is a workbook with some simple test data (feel free to add some more parts of your own), and a macro called EnumerateRange that you can execute that will expand all part strings in the first column.  Give it a try and see what you think.  We can enhance it a bit before calling it done, but it seems to be pretty close.

EE29074523.xlsm


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.