Converting 1995 - 2001 field into 1995,1996,1997,1998,1999,2000,2001 etc

[chrisjako]

Can anyone help with getting a number range to convert to a sequential list of numbers?

I have columns with fields such as "1995 - 2001" or "2005 - 2018" which i want to convert into a sequence of numbers in the corresponding row former example:

1995,1996,1997,1998,1999,2000,2001

Thanks in advance

 

[mse330]

This is a UDF (User Defined Function) in VBA ... Just copy the below code in VBA Module and assuming you have your data in cell A1 "1995 - 2001" then type in B1 the following formula =YearsRg(A1)

Function YearsRg(txt As String) As String
'You can change the name of the function "YearsRg" to whatever you desire

For x = 0 To Right(txt, 4) - Left(txt, 4)
    YearsRg = YearsRg & Comma & Left(txt, 4) + x 'If you change the function name, you have to change it in this line too
    Comma = ","
Next x

End Function

Hope that helps & let me know if you have any question

 

[Rick Rothstein]

This is a UDF (User Defined Function) in VBA ... Just copy the below code in VBA Module and assuming you have your data in cell A1 "1995 - 2001" then type in B1 the following formula =YearsRg(A1)

Function YearsRg(txt As String) As String
'You can change the name of the function "YearsRg" to whatever you desire

For x = 0 To Right(txt, 4) - Left(txt, 4)
    YearsRg = YearsRg & Comma & Left(txt, 4) + x 'If you change the function name, you have to change it in this line too
    Comma = ","
Next x

End Function

You can write your function as a one-liner (hence, no loop)...

[table="width: 500"]
[tr]
	[td]Function YearsRg(S As String) As String
  YearsRg = Join(Evaluate("TRANSPOSE(ROW(" & Replace(Replace(S, " ", ""), "-", ":") & "))"), ",")
End Function[/td]
[/tr]
[/table]

 

[mse330]

Thanks Rick for the one-liner code … I still don't fully understand the Evaluate function

 

[chrisjako]

WOW - thanks this works a treat and in the perfect format i need too - big thanks to you both

 

[mse330]

Glad we could help & thanks for reporting back

 

[Marcelo Branco]

You can write your function as a one-liner (hence, no loop)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function YearsRg(S As String) As String
  YearsRg = Join(Evaluate("TRANSPOSE(ROW(" & Replace(Replace(S, " ", ""), "-", ":") & "))"), ",")
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Nice!

M.

 

[chrisjako]

Sorry one last question, how would I convert it so the year range was something like 84 - 92 instead of 1984 - 1992

so my source field is 84 - 92 and my result field would be 1984,1985,1986,1987,1988,1989,1990,1991,1992

 

[mse330]

What is the purpose of this ? Date of births ? I am asking because we have to know if 12 - 18 means 1912 - 1918 or 2012 - 2018 …. Can we take a cut-off year for example anything below 70 should be 1970 and 71+ should be 2071

 

[chrisjako]

The 2 digits are just years, they would never go less than 1975 to current 2018