Create Number Sequence with VBA Custom Function

Recently I had some data presented as numbers in a cell separated by a dash. I wanted the numbers in between so needed to get creative. The original data looked like this:







It was the numbers that were not showing that I was really interested in. Here is a more detailed example of the start data and the end result required.

Add number sequence with VBA custom function.

Add number sequence with VBA custom function.

As you can see from above the data needs to be presented like the following.


In the next column across. Well this would take quite some time to do manually so I tested the waters with a custom function. Here are the results:

Option Explicit

Function Sequence(txt As String) As String
Dim i As Long
Dim j
     For Each j In Split(txt, ",")
          If j Like "*-*" Then
               For i = Split(j, "-")(0) To Split(j, "-")(1)
                    Sequence = Sequence & "," & i
               Next i
            Sequence = Sequence & "," & j
            End If
     Next j
Sequence = Mid$(Sequence, 2)
End Function

The custom function will split the data based on the delimiter “-” so if your data is split differently change the delimiter. Also if you don’t want the data separated by a comma if you wanted a space for example, then change this line.

Sequence = Sequence & “ “ & j

Where the “ “ is representative of a space.

Hope this article is exactly what you were looking for. I have included the file which should aid in crystallising the concept.