Find/replace sequence range of numbers 1-10

[aleon]

Hi all,

New to the forums, keep up the good work.

Getting stuck on getting a cell with a value of 1-10, separated out into its individual values i.e 1,2,3,4,5,6,7,8,9,10
Another example is 50-60 again, i want to separated the values out like: 50,51,52... and so on...

Can anyone help?

I managed to get some info how it might be possible, but gettig stuck on a working formula in Excel 2016.

<code>=SUBSTITUTE(TRIM(CONCAT(ROW(INDIRECT(LEFT(A1,FIND("-",A1)-1)&":"&MID(A1,FIND("-",A1)+1,999)))&" "))," ",",")</code>

Excel complains on the <code>A1,FIND portion where trying to find values separated by a hyphen =-=

Hoe someone can help?

Thanks very much.
</code>

 

[Rick Rothstein]

Unfortunately this expression/function just returns what is the difference (subtraction) between the number sin the range.
i.e. 123456789-123456799

10

The technique Peter used in his code was to make the numbers on either side of the dash row numbers and let Excel generate the list; however, those numbers are larger than what any row number can be. I think this UDF (user defined function should work correctly for you...

[table="width: 500"]
[tr]
	[td]Function Seq(sLimits As String, Optional sDelimiter As String = ",") As Variant
  Dim Prefix As String, ReCombo As String, S As Variant, Parts As Variant
  For Each S In Split(Replace(sLimits, " ", ""), ",")
    If InStr(S, "-") Then
      Parts = Split(S, "-")
      If Len(Parts(0)) > 6 Then
        Parts = Split(S, "-")
        Prefix = Left(Parts(0), Len(Parts(0)) - 6)
        Parts(0) = "A" & Mid(Parts(0), Len(Parts(0)) - 5)
        If UBound(Parts) = 1 Then Parts(1) = "A" & Mid(Parts(1), Len(Parts(1)) - 5)
      End If
      S = Join(Evaluate("TRANSPOSE(ROW(" & Join(Parts, ":") & "))"), sDelimiter)
      If Len(Prefix) Then S = Prefix & Replace(S, sDelimiter, sDelimiter & Prefix)
    End If
    Seq = Seq & sDelimiter & S
  Next
  Seq = Mid(Seq, Len(sDelimiter) + 1)
End Function[/td]
[/tr]
[/table]

 

[aleon]

Hi @Rick Rothstein

Thanks for your advise and input into this. Sorry has taking this long for me to respond, didn't get the alert of a new response to this thread.

Have tried your UDF and works fine with range of number 0123-1234 (4-digits) long but if its 5,6 or higher number of digits long the UDF can transpose these number, example: 78934-78937 get #VALUE ! response back.

Much appreciated you and everyone else response, excel is powerful tool if you know how too utilize its calcualtion power

Thanks

 

[Rick Rothstein]

Hi @Rick Rothstein

Have tried your UDF and works fine with range of number 0123-1234 (4-digits) long but if its 5,6 or higher number of digits long the UDF can transpose these number, example: 78934-78937 get #VALUE ! response back.

Are you talking about the code I posted in Message #31 ?

 

[aleon]

Yes thats the one Rik, thanks.

 

[aleon]

Hi @Rick Rothstein
Got any ideas on my post at #33 thanks so much!

 

[Rick Rothstein]

Hi @Rick Rothstein
Got any ideas on my post at #33 thanks so much!

When I try my code with the "problem" example you posted in Message #32 , the function returns the correct value for me. Given I can not duplicate your problem, I am at a loss how to solve it for you.

 

[aleon]

OK Thanks Rik, might be problem with Excel version in use my end then.
Will keep trying and saving/re-saving file in old/new versions see if i can find a working pattern for me and post here.