Macro to expand a range of alphanumeric text

[blooze_hound]

Hello,
I'm looking for a macro for Excel 2007 to expand alphanumeric text contained in a column. For example cell A:1 may contain TP1-6 TP30-TP34 which would be expanded into TP1, TP2, TP3, TP4, TP5, TP6, TP30, TP31, TP32, TP33, TP34 and placed into an empty cell in B:1. Cell A:2 may contain X1-X19, expanded to X1, X2, X3, X4, etc and placed into an empty cell in B:2. Any cells without ranges it would need to copy the contents to the adjacent empty cell. This would then be repeated for the entire column with varying text before the numbers (R12-23, JP3-7 etc) until a blank cell is reached. The range will always be delineated with the dash.
Thanks in advance for your help.

Excel 2007

	A	B
1	TP1-6 TP30-34	TP1, TP2, TP3, TP4, TP5, TP6, TP30, TP31, TP32, TP33, TP34
2	R1	R1
3	R13, R14, R15, R16	R13, R14, R15, R16
4	X19-X20	X19,X20
5	R3 R9 R10 R26	R3 R9 R10 R26

<tbody>
</tbody>

 

[pgc01]

I hope you won't take offense at my comments, but I do not like how your code handles "problem" series. I know you said (in Message #14)...

"Remark: I assume the input string has no syntax errors"

but mistakes happen, ...

1 - NO! I don't take offense and I welcome your comments in this thread and in any other. I learn from them.

2 - For the solution I posted, mistakes don't happen, that's what my assumption says. Think of it like the input is automatically generated. For ex., you get your bank statement for last month and you have the list with all your transactions. All those transactions have a known format and there are no mistakes (hopefully).

3 - I could also answer another problem, the problem you are answering, in which there may be mistakes.

In that case my solution would not mix expanding the ranges and dealing with input errors.
I would have 2 pieces of code that would run in sequence (unless Step 1 detects errors, in which case the program doesn't go on to step 2):

Step 1 - a code that validates the input text, and if that is the case signals the errors found
Step 2 - a code to expand the ranges

You can think the solution I posted as the Step 2.

This means that your solution is more complete than mine as it includes both validating the input and expanding the ranges, whilst mine only expands the ranges.

I'm really not interested in doing Step 1 or going much deeper into this problem. As you know this is already the nth iteration, with specifications changing with each post. A good example of this is how you start your last post "Well, that changes everything.". I just posted solutions that solve the expansion of the ranges part of what is my current understanding (/guessing) of the problem.

 

[pawanhsharma]

I have checked with both of your solutions. Below is a good example of what I am looking at.
Rev codes 0730, 0750, 0490, 0258, 0769 CPT T5355-T5360, 45342-45345, 4530S-4535S, G0328, 45333-45340, 45341, G0120-G0121, G0104-G0105, 4537R-4540R Base 20, V20.1- V20.6, M20.1-20.4
I have a sheet with hundreds of cells containing criterias like mentioned as about these are Healthcare codes. I am not able to use this in my formulas and codes untill and unless the ranges and seperated.
The solution provided by you works except for the Dignosis codes which contain a Dot. and the solution provided by Rick not working at all, I might not using it properly, as his previous codes was working good.
Still thanks for all your help, you both are Guru's in Excel and VBA

 

[pawanhsharma]

I have come across situation as they are implementing new ICD10 coding system I have code ranges where ALPHA character is not at end or starting position e.g. 123T22 - 123T45. now here I know the initial 4 letters are fixed regardless they are alpha or numeric. can we change the code where we can put the fixed char so it will not matter what position the alpha char is.

 

[pawanhsharma]

I have come across situation as they are implementing new ICD10 coding system I have code ranges where ALPHA character is not at end or starting position e.g. 123T22 - 123T45. now here I know the initial 4 letters are fixed regardless they are alpha or numeric. can we change the code where we can put the fixed char so it will not matter what position the alpha char is.

Any help ?

 

[pawanhsharma]

I found a turnaround for this but not a complete solution. for the range like 123T45-123T60 I put a formula like

=SUBSTITUE(ExpandSeries(SUBSTITUTE(A11,"123T","123")),"123","123T")

and it worked but as there are hundreds f rows it is not a solution.

This can be done if we compare the start and end code for common part ('i.e. 123T) remove it and put it back when the list populated, also there could be alphabet at ending.