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:
Data
100-110
210-217
112-118
115-120
134-138
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.
As you can see from above the data needs to be presented like the following.
100,101,102,103…110
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
Else
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.
