Of late I have been working with pulling data from cells and extracting the numbers between a series. So if the data is presented like the following.
(2010-2015)
I want to see the following:
2010, 2011,2012, 2013, 2014, 2015
This post is very similar to the blog post from January which does the same sort of thing differently:
To do this we could create a custom function to pull the data from a cell in a neat and clean way.
Option Explicit
Function GetYr(txt As String) As String
Dim Var As Variant
If txt Like "*-*" Then
Var = Split(txt, "-")
GetYr= Join(Evaluate("transpose(row(" & Var(0) & ":" & Var(1) & "))"), ", ")
Else
GetYr= Val(txt)
End If
End Function
I prefer the extract method as it is cleaner and uses less coding to achieve a customised result. Use the dash *-* as your delimiter, if you want to change it use your symbol in place of the dash.
Neat and clean result with a custom function drawing on the Evaluate function.
Also if you wish to have a different data separator then change the following;
Years = Join(Evaluate("transpose(row(" & Var(0) & ":" & Var(1) & "))"), ", ")
Where ", "
The end needs to change if you wished to use for example the dash then the code would look as follows.
Years = Join(Evaluate("transpose(row(" & Var(0) & ":" & Var(1) & "))"), "- ")
Where "- "
Is the part you change. Hope this is clear.
The below file shows the coding in action. Hope this provides more clarity.
