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

[Rick Rothstein]

To some extent, the code in this article performs a similar action to the one I posted here...

Return individual page numbers from a list of pages and ranges (e.g., 1,5,9-12,20-18)

except that it allows for the numbers to have text prefixes (as shown in this article's title example), for there to be mixed series using different text prefixes and for the delimiter between series to be spaces and/or commas. So, a small sampling of the kinds of input which the UDF below will be able to process would be...

1,5,9-12,20-18
1 5 9-12 20-15
1,5 9-12, 20 - 15
AB8-AB12
CD3 - CD7 K4, P2-P5 JM8 - JM3
ABCD1-9
XYZ3 - 7

The things to note about the above series is you can mix the space and/or comma delimiter between series items at will, series items be just one item (no dash), the numeric parts of a dashed series can advance either up (A1-A5) or down (B5-B1) and the series will be produced in that order, and the second part of a dashed series can have the text prefix omitted (the text prefix from the first part of that dashed series will be automatically assumed to apply to the second part). One thing which does not show up in the above list, but is permitted, is multiple spaces... you are not restricted to single spaces, use as many as you want as the extra ones will be thrown away during processing. Okay, here is the code that perform the "magic"...

Function ExpandedSeries(ByVal S As String, Optional Delimiter As String = ", ") As Variant
  Dim X As Long, Y As Long, Z As Long
  Dim Letter As String, Numbers() As String, Parts() As String
  S = Chr$(1) & Replace(Replace(Replace(Replace(Application.Trim(Replace(S, ",", _
      " ")), " -", "-"), "- ", "-"), " ", " " & Chr$(1)), "-", "-" & Chr$(1))
  Parts = Split(S)
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*-*" Then
      For Z = 1 To InStr(Parts(X), "-") - 1
        If IsNumeric(Mid(Parts(X), Z, 1)) And Mid$(Parts(X), Z, 1) <> "0" Then
          Letter = Left(Parts(X), Z + (Left(Parts(X), 1) Like "[A-Za-z" & Chr$(1) & "]"))
          Exit For
        End If
      Next
      Numbers = Split(Replace(Parts(X), Letter, ""), "-")
      If Not Numbers(1) Like "*[!0-9" & Chr$(1) & "]*" Then Numbers(1) = Val(Replace(Numbers(1), Chr$(1), "0"))
      On Error GoTo SomethingIsNotRight
      For Z = Numbers(0) To Numbers(1) Step Sgn(-(CLng(Numbers(1)) > CLng(Numbers(0))) - 0.5)
        ExpandedSeries = ExpandedSeries & Delimiter & Letter & Z
      Next
    Else
      ExpandedSeries = ExpandedSeries & Delimiter & Parts(X)
    End If
  Next
  ExpandedSeries = Replace(Mid(ExpandedSeries, Len(Delimiter) + 1), Chr$(1), "")
  Exit Function
SomethingIsNotRight:
  ExpandedSeries = CVErr(xlErrValue)
End Function

[snb]

Hi Rick,

I did it 'my way':

Function F_expand(c00)
  sn = Split(Replace(Replace(Replace(Trim(c00), " -", "-"), "- ", "-"), ",", " "))
  
  For j = 0 To UBound(sn)
    If InStr(sn(j), "-") Then
       If Val(sn(j)) = 0 Then c01 = Left(sn(j), InStr(sn(j), "-") - Len(Format(Val(StrReverse(Left(sn(j), InStr(sn(j), "-")))))))
       sp = Split(Replace(sn(j), c01, ""), "-")
        
       If Val(sp(0)) > Val(sp(1)) Then c02 = Val(sp(0)) + Val(sp(1)) & "-"
        sn(j) = c01 & Join(Evaluate("transpose(" & c02 & "row(" & sp(0) & ":" & sp(1) & "))"), "," & c01)
    End If
  Next
  
  F_expand = Join(sn, ",")
End Function

[Rick Rothstein]

@snb,

I get a #VALUE! error for the following (which was the first "weird" sequence I tried) with your UDF...

a9 - 4 x4,m9 r7 b4 - b8,c4-1

[snb]

@Rick

I tested only with your offered strings:

1,5,9-12,20-18
1 5 9-12 20-15
1,5 9-12, 20 - 15
AB8-AB12
CD3 - CD7 K4, P2-P5 JM8 - JM3
ABCD1-9
XYZ3 - 7

This doesn't error out on my system however.

Sub tst()
    MsgBox F_expand("a9 - 4 x4,m9 r7 b4 - b8,c4-1")
End Sub

Function F_expand(c00)
  sn = Split(Replace(Replace(Replace(Trim(c00), " -", "-"), "- ", "-"), ",", " "))
  
  For j = 0 To UBound(sn)
    If InStr(sn(j), "-") Then
       c01 = ""
       c02 = ""
       If Val(sn(j)) = 0 Then c01 = Left(sn(j), InStr(sn(j), "-") - Len(Format(Val(StrReverse(Left(sn(j), InStr(sn(j), "-")))))))
       sp = Split(Replace(sn(j), c01, ""), "-")
        
       If Val(sp(0)) > Val(sp(1)) Then c02 = Val(sp(0)) + Val(sp(1)) & "-"
        sn(j) = c01 & Join(Evaluate("transpose(" & c02 & "row(" & sp(0) & ":" & sp(1) & "))"), "," & c01)
    End If
  Next
  
  F_expand = Join(sn, ",")
End Function

[Rick Rothstein]

Sub tst()
MsgBox F_expand("a9 - 4 x4,m9 r7 b4 - b8,c4-1")
End Sub[/CODE]

I am not sure what went wrong the first time (I checked every at the time when I reported it not working), but based on the above, I decided to test it again (hoping to spot why it worked in a MessageBox but not on a worksheet) and lo-and-behold, now it is working. I cannot explain why it didn't work the first time, but I can confirm it does in fact work for that text string (haven't tested any others yet, but I wanted to get this correction out there hoping to save you some investigation time. Sorry for the earlier misdirection.

[Rick Rothstein]

I am not sure what went wrong the first time (I checked every at the time when I reported it not working), but based on the above, I decided to test it again (hoping to spot why it worked in a MessageBox but not on a worksheet) and lo-and-behold, now it is working. I cannot explain why it didn't work the first time, but I can confirm it does in fact work for that text string (haven't tested any others yet, but I wanted to get this correction out there hoping to save you some investigation time. Sorry for the earlier misdirection.

I think I may have spoken too soon. For this value...

a9 - 4 x4,m9 r7 b4 - b8,c4-1

your code presents the red highlighted range backwards and each number is one too high(b9,b8,b7,b6,b5 instead of b4,b5,b6,b7,b8). Also, if you decide to try and fix this, could you make the delimiter easier to set (you use "," whereas I used ", ", but someone might want "/-/", and I found it hard to set this correctly within your code.

[snb]

@rick,

You should use the latest code I posted, which contains c01="" and c02="" to avoid 'backward' counting when it's not appropriate.

To adapt the delimiter:

F_expand = replace(Join(sn, ","),",","/-/")

[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!