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.
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:
Function Sequence(txt As String) As String
Dim i As Long
For Each j In Split(txt, ",")
If j Like "*-*" Then
For i = Split(j, "-")(0) To Split(j, "-")(1)
Sequence = Sequence & "," & i
Sequence = Sequence & "," & j
Sequence = Mid$(Sequence, 2)
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.