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

#### chrisjako

##### New Member
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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### mse330

##### Well-known Member
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)

Rich (BB code):
``````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

Last edited:

#### Rick Rothstein

##### MrExcel MVP
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)
Rich (BB code):
``````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)...
Code:
``````[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

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

#### chrisjako

##### New Member

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

#### mse330

##### Well-known Member
Glad we could help & thanks for reporting back

#### Marcelo Branco

##### MrExcel MVP

You can write your function as a one-liner (hence, no loop)...
Code:
``````[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

##### New Member
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

##### Well-known Member
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

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

Replies
4
Views
62
Replies
13
Views
219
Replies
7
Views
93
Replies
5
Views
62
Replies
11
Views
127