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

chrisjako

New Member
Joined
Apr 10, 2014
Messages
26
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
 

Some videos you may like

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
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
Thanks Rick for the one-liner code … I still don't fully understand the Evaluate function :)
 

chrisjako

New Member
Joined
Apr 10, 2014
Messages
26

ADVERTISEMENT

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

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for reporting back
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393

ADVERTISEMENT

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! :cool:

M.
 

chrisjako

New Member
Joined
Apr 10, 2014
Messages
26
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
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,238
Members
409,857
Latest member
KailuaTown
Top