1

For example, I have 33 in cell A1, and 40 in cell B1, I want a formula in cell C1, to write an arithmetic sequence from 33 to 40 like this: 33, 34, 35, 36, 37, 38, 39, 40

example

phuclv
  • 26,555
  • 15
  • 113
  • 235
Saher Naji
  • 45
  • 5
  • Could you show us your attempts? – Toto Jun 19 '22 at 20:29
  • actually the numbers I have like this: for example: 33-38 in cell A1, I tried to right this formula: "=RIGHT(B1,2)-LEFT(A1,2)" the result will be 5, I thought that I can use this number to generate a sequence starting from 33 (5) times to reach to 38 – Saher Naji Jun 19 '22 at 20:34
  • But I did not find a formula to help me to generate this sequence – Saher Naji Jun 19 '22 at 20:37

1 Answers1

0

In Office 365:

C1: =TEXTJOIN(", ",TRUE,SEQUENCE(B1-A1+1,,A1))

sample output

Edit: If you need to use this in an earlier version of Excel, and your input is a hyphenated string (as you show in your example in your comment) denoting the start and end numbers, try:

 Option Explicit
 Function Consec(ln As String) As String
    Dim v As Variant, w As Variant
    Dim L As Long, E As Long
    
w = Split(ln, "-")
L = w(0)
E = w(1)

ReDim v(L To E)
For L = L To E
    v(L) = L
Next L

Consec = Join(v, ", ")
End Function

eg: Consec("33-40") => 33, 34, 35, 36, 37, 38, 39, 40

phuclv
  • 26,555
  • 15
  • 113
  • 235
Ron Rosenfeld
  • 8,198
  • 3
  • 13
  • 17
  • It's working perfect on Office 365, thank you, by the way I have to use it on Excel 2013, so I used this code: Function CONSEC2(ln As Variant) Dim rn As Integer rn = 1 * Right(ln, Len(ln) - InStr(1, ln, "-")) ln = 1 * Left(ln, InStr(1, ln, "-") - 1) With CreateObject("Scripting.Dictionary") For i = ln To rn .Add i, True Next i CONSEC2 = Join(.keys, ", ") End With End Function – Saher Naji Jun 20 '22 at 06:47
  • @SaherNaji See my edited response for a slightly different method (tested in Windows Excel) – Ron Rosenfeld Jun 20 '22 at 10:03