Generalized Series Expansions (e.g. AB5-AB9 becomes AB5, AB6, AB7, AB8, AB9)

[jcoeng]

hello, I found your post from another forum and posted a reply there, but another user had done the same and there wasn't a reply so I thought I'd try the original. Previous question I posted is here and I can update if there's a response: https://www.mrexcel.com/forum/excel-...-prefixes.html

@Rick Using your UDF...So far this is a great UDF! I've used it with great success on several lists however I did find some issues, I have some ranges that it doesn't seem to like. Some examples are J9-11 and C7-11 or pretty much anything that starts with a letter and 7 or 9 and is a range 7-11, 9-13 etc.

any ideas how to resolve this?

@SNB Looking at your code I'm not sure how to put that into a module for use. Does it require 2 modules with both sets of code? You also mentioned updating the code but I couldn't find the update integrated in one of the above codes. Sorry quite lacking on coding knowledge.

Any thoughts on either would be greatly appreciated!

Thanks!

[jcoeng]

Well, I did some more investigating and it seems the errors I was encountering with Ricks UDF was related to changing the number of digits at each end of the ranges. Example a 1 digit to 2 digit range such as 9-11 or 99-101, these would give no result

I did get SNB's code to work finally once I understood what was going on, sorry I'm a bit slow, simply copied and pasted and it worked well. The only change I made was adding a space after each , delimiter.

I did this by changing this line

sn(j) = c01 & Join(Evaluate("transpose(" & c02 & "row(" & sp(0) & ":" & sp(1) & "))"), "," & c01)

to this (adding the space near the end changing "," to ", " )

sn(j) = c01 & Join(Evaluate("transpose(" & c02 & "row(" & sp(0) & ":" & sp(1) & "))"), ", " & c01)

Thanks again for the helpful code guys!