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>

 

[aleon]

Peter_SSs
Using your Seq formula is it possible to extrapolate the series of number from the same cells when it also contains multiple series + individual numbers in the mix?

For example:
A1 = 1,2,3, 5-20, 100,101,102, 700-708

and thx for the welcome

 

[Peter_SSs]

Peter_SSs
Using your Seq formula is it possible to extrapolate the series of number from the same cells when it also contains multiple series + individual numbers in the mix?

For example:
A1 = 1,2,3, 5-20, 100,101,102, 700-708

Sure, try this function.

Function MultiSeq(sLimits As String, Optional sDelimiter As String = ",") As String
  Dim s As Variant
  
  For Each s In Split(Replace(sLimits, " ", ""), ",")
    MultiSeq = MultiSeq & sDelimiter & Join(Application.Transpose(Evaluate("row(" & Join(Application.Index(Split(s & "-" & s, "-", 3), 1, Array(1, 2)), ":") & ")")), sDelimiter)
  Next s
  MultiSeq = Mid(MultiSeq, Len(sDelimiter) + 1)
End Function

Excel Workbook
	A	B
1	1,2,3, 5-20, 100,101,102, 700-708	1,2,3,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,100,101,102,700,701,702,703,704,705,706,707,708
Multiple Sequences

 

[aleon]

This is super thank you so much Peter_SSs

 

[Peter_SSs]

This is super thank you so much Peter_SSs

No problem. Glad to help.

 

[aleon]

Hi Peter,

Hope you dont mind me asking again, keep asking questions here

From your formula above how can I extend the number of digits that can be extrapolated?

I mean, if the number(s) or ranges within a cell are 10-digits/or longer, how can I update "function" to deal with these longer numbers, as I think if its more than 6-digits its ignored by "function".

Thanks once again, appreciate you expert advise on this.

 

[aleon]

Peter_SSs
Hoping this tag works and can help again

 

[Rick Rothstein]

[table="width: 500"]
[tr]
	[td]Function Seq(sLimits As String, Optional sDelimiter As String = ",") As Variant
  Seq = Join(Application.Transpose(Evaluate("row(" & Replace(sLimits, "-", ":") & ")")), sDelimiter)
End Function[/td]
[/tr]
[/table]

Is there an advantage to using Application.Transpose rather than embedding TRANSPOSE directly inside the Evaluate function call?

[table="width: 500"]
[tr]
	[td]Function Seq(sLimits As String, Optional sDelimiter As String = ",") As Variant
  Seq = Join(Evaluate("TRANSPOSE(ROW(" & Replace(sLimits, "-", ":") & "))"), sDelimiter)
End Function[/td]
[/tr]
[/table]

 

[Peter_SSs]

Peter_SSs
Hoping this tag works and can help again

I'm sorry, I haven't been able to spend time on the forum for a while and that will continue for a while longer. Perhaps Rick or somebody else might be able to help.

 

[aleon]

Thank you both for continue support.
Appreciate the time, we all have busy lives and not that we get paid for this.

But thank you!

Will try out your version @Rick Rothstein

 

[aleon]

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

123456789-123456799

10

<tbody>
</tbody>

Is there an advantage to using Application.Transpose rather than embedding TRANSPOSE directly inside the Evaluate function call?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function Seq(sLimits As String, Optional sDelimiter As String = ",") As Variant
  Seq = Join(Evaluate("TRANSPOSE(ROW(" & Replace(sLimits, "-", ":") & "))"), sDelimiter)
End Function[/TD]
[/TR]
</tbody>[/TABLE]