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]

Try the below modified code

Function YearsRg(txt As String) As String

Dim Y1 As Double, Y2 As Double, Yrs As Double
Y1 = Left(txt, 2)
Y2 = Right(txt, 2)

If Y1 >= 75 Then Y1 = Y1 + 1900 Else Y1 = Y1 + 2000
If Y2 >= 75 Then Y2 = Y2 + 1900 Else Y2 = Y2 + 2000
Yrs = Y2 - Y1

For x = 0 To Yrs
    YearsRg = YearsRg & Comma & Y1 + x
    Comma = ","
Next x

End Function

 

[chrisjako]

Try the below modified code

Function YearsRg(txt As String) As String

Dim Y1 As Double, Y2 As Double, Yrs As Double
Y1 = Left(txt, 2)
Y2 = Right(txt, 2)

If Y1 >= 75 Then Y1 = Y1 + 1900 Else Y1 = Y1 + 2000
If Y2 >= 75 Then Y2 = Y2 + 1900 Else Y2 = Y2 + 2000
Yrs = Y2 - Y1

For x = 0 To Yrs
    YearsRg = YearsRg & Comma & Y1 + x
    Comma = ","
Next x

End Function

Big thank you to you its perfect!

 

[mse330]

You are welcome ... Glad to help

 

[chrisjako]

If the Y2 was to equal "UP" i would want to replace this with 2018 (or next year 2019)

I thought this may work in your formula but it just gives a #VALUE

Function YearsRg(txt As String) As String


Dim Y1 As Double, Y2 As Double, Yrs As Double
Y1 = Left(txt, 2)
Y2 = Right(txt, 2)


If Y2 = "UP" Then Y2 = "2018"
If Y1 >= 75 Then Y1 = Y1 + 1900 Else Y1 = Y1 + 2000
If Y2 >= 75 Then Y2 = Y2 + 1900 Else Y2 = Y2 + 2000


Yrs = Y2 - Y1


For x = 0 To Yrs
YearsRg = YearsRg & Comma & Y1 + x
Comma = ","
Next x


End Function

 

[mse330]

Y2 is defined as "double" data type which doesn't accept text "string" & in the modification you did, you are assigning a value of "UP" to the variable & that's why it is giving you an error. To fix it, change the data type of Y2 to "String" instead of "double" in the first line of code. Also, you may change the new added line to this

If Y2 = "UP" Then Y2 = "2018"​
' Change the above line to this 

If Y2 = "UP" Then Y2 = Year(Date) - 2000 ' <-- This will always give you current year

 

[Rick Rothstein]

Here is my original function modified so that your can give it 4-digit range of years (for example, 1997-2003) or a 2-digit range of years (for example 97-03) or a mixture of the two (for example, either 1997-03 or 97-2003) and, if the last year is the word "UP" (in any letter casing), then the current year will replace it.

[table="width: 500"]
[tr]
	[td]Function YearsRg(S As String) As String
  Dim Yrs() As String
  Yrs = Split(S, "-")
  If UCase(Yrs(1)) = "UP" Then Yrs(1) = Year(Now)
  If Len(Yrs(0)) < 4 Then Yrs(0) = Yrs(0) + 1900 - 100 * (Yrs(0) < 75)
  If Len(Yrs(1)) < 4 Then Yrs(1) = Yrs(1) + 1900 - 100 * (Yrs(1) < 75)
  YearsRg = Join(Evaluate("TRANSPOSE(ROW(" & Yrs(0) & ":" & Yrs(1) & "))"), ",")
End Function[/td]
[/tr]
[/table]

 

[chrisjako]

Here is my original function modified so that your can give it 4-digit range of years (for example, 1997-2003) or a 2-digit range of years (for example 97-03) or a mixture of the two (for example, either 1997-03 or 97-2003) and, if the last year is the word "UP" (in any letter casing), then the current year will replace it.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function YearsRg(S As String) As String
  Dim Yrs() As String
  Yrs = Split(S, "-")
  If UCase(Yrs(1)) = "UP" Then Yrs(1) = Year(Now)
  If Len(Yrs(0)) < 4 Then Yrs(0) = Yrs(0) + 1900 - 100 * (Yrs(0) < 75)
  If Len(Yrs(1)) < 4 Then Yrs(1) = Yrs(1) + 1900 - 100 * (Yrs(1) < 75)
  YearsRg = Join(Evaluate("TRANSPOSE(ROW(" & Yrs(0) & ":" & Yrs(1) & "))"), ",")
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Got VALUE again at first but my column C2 if formatted like this "08 - UP" - so I stuck a space in the

If UCase(Yrs(1)) = " UP" Then Yrs(1) = Year(Now)

and this worked perfectly Rick, thanks again I am in awe of your brilliance, kindness and talent!

 

[Rick Rothstein]

Got VALUE again at first but my column C2 if formatted like this "08 - UP" - so I stuck a space in the

If UCase(Yrs(1)) = " UP" Then Yrs(1) = Year(Now)

and this worked perfectly Rick

If you use this version of my code, it will still work as I described in Message #16 , but it will not matter if you have one or more spaces around your dashes or if you slip up and forget to put any spaces around them.

[table="width: 500"]
[tr]
	[td]Function YearsRg(S As String) As String
  Dim Yrs() As String
  Yrs = Split(S, "-")
  If UCase(Trim(Yrs(1))) = "UP" Then Yrs(1) = Year(Now)
  If Len(Trim(Yrs(0))) < 4 Then Yrs(0) = Yrs(0) + 1900 - 100 * (Yrs(0) < 75)
  If Len(Trim(Yrs(1))) < 4 Then Yrs(1) = Yrs(1) + 1900 - 100 * (Yrs(1) < 75)
  YearsRg = Join(Evaluate("TRANSPOSE(ROW(" & Trim(Yrs(0)) & ":" & Trim(Yrs(1)) & "))"), ",")
End Function[/td]
[/tr]
[/table]

 

[chrisjako]

Great Rick thanks, you have helped me immensely so a big thank you

 

[shaowu459]

If you are using Office 365, the TEXTJOIN funcition will be available. It is easy to solve the problem using Textjoin:

=TEXTJOIN(",",,ROW(INDIRECT(SUBSTITUTE(A1," - ",":"))))

Ctrl shift enter for the array formula.