Excel: Listing Numbers In Between 2 Numbers

Question

I was wondering if anyone knew of a formula to list all the numbers between 2 values, so for example if cell F2 had 12 in it and G2 had 17 in it I'd like a formula that would show 13,14,15,16 In cell H2.

Thanks

Answer 1

This cannot be done with an Excel worksheet function. You will need VBA for that. It can be done with a user-defined function (UDF).

The following code needs to be stored in a code Module. You need to right-click the sheet tab, select View Code. This will open the Visual Basic Editor. Click Insert > Module and then paste the following code:

Function InBetween(MyFirst As Integer, MyLast As Integer)
Dim foo As String
Dim i As Long
foo = MyFirst + 1
For i = MyFirst + 2 To MyLast - 1
    foo = foo & "," & i
Next i
InBetween = foo
End Function

Now you can use a formula like =InBetween(F2,G2) to produce the result you describe.

You need to save the file as a macro-enabled workbook with the XLSM extension. See screenshot for code and user defined function in action.