Adding a Sequence of Consecutive Years in Between a Range of years

[benvillacrez]

I know the title may not sound so clear so here is an example of what I trying to accomplish.

Example:

I have a predetermine range of years (Column A) and I want to add consecutive number in between them like (Column B)

[Column A]
2010-2015
2008-2012
2011-2015
1998-2002
2001-2003



[Column B]
2010, 2011, 2012, 2013, 2013, 2015
2008, 2009, 2010, 2011, 2012
2011, 2012, 2013, 2014, 2015
1998, 1999, 2001, 2002
2001, 2002, 2003


I look forward to hearing back from you guys.

Thanks in advance.

[benvillacrez]

--------------------Bump-----------------------------

[dflak]

You can set this up as a UDF

Please Login or Register  to view this content.

[FDibbins]

Hi ben, welcome to the forum

Forum rules require you to wait at least a day before bumping,

Having said that, why does this have to all be in 1 cell? It's not like you could (easily) use any on that in a formula. Have you considered using different columns?

[benvillacrez]

Hi ben, welcome to the forum

Forum rules require you to wait at least a day before bumping,

Having said that, why does this have to all be in 1 cell? It's not like you could (easily) use any on that in a formula. Have you considered using different columns?

Thank you! I'm sorry I did not know that. I just a a little frustrated trying to figure this issue. As for your question. It does not necessarily have to be in the same column it can be in multiple columns as long as I'm able to add the years in between within columns is fine and can later group them together with commas.

i can't seem to be able to create the the consecutive years in between the predetermine range of years

[benvillacrez]

You can set this up as a UDF

Please Login or Register  to view this content.

Thank you for taking your valuable time to address my question. I'm total unexpired when it comes to excel. Can you kindly elaborate as to how can i apply this function. Do I just select the columns that i want to split the years and apply the UDA?

Thanks,
Benjamin

[FDibbins]

I have done this with a bunch of helper columns, that you can hide if needed...

	A	B	C	D	E	F	G	H	I	J
1	2010-2015	2010,2011,2012,2013,2014,2015	2010	2015	2010	2011	2012	2013	2014	2015
2	2008-2012	2008,2009,2010,2011,2012	2008	2012	2008	2009	2010	2011	2012
3	2011-2015	2011,2012,2013,2014,2015	2011	2015	2011	2012	2013	2014	2015
4	1998-2002	1998,1999,2000,2001,2002	1998	2002	1998	1999	2000	2001	2002
5	2001-2003	2001,2002,2003	2001	2003	2001	2002	2003

B1=SUBSTITUTE(TRIM(E1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1)," ",",")
C1=IFERROR(--LEFT(A1,4),"")
D1=IFERROR(--RIGHT(A1,4),"")
E1=C1
F1=IFERROR(IF(E1+1>$D1,"",E1+1),"")....copied across as needed

Then copy that whole mess down as far as you need. Then hide columns C:??

[benvillacrez]

I have done this with a bunch of helper columns, that you can hide if needed...

	A	B	C	D	E	F	G	H	I	J
1	2010-2015	2010,2011,2012,2013,2014,2015	2010	2015	2010	2011	2012	2013	2014	2015
2	2008-2012	2008,2009,2010,2011,2012	2008	2012	2008	2009	2010	2011	2012
3	2011-2015	2011,2012,2013,2014,2015	2011	2015	2011	2012	2013	2014	2015
4	1998-2002	1998,1999,2000,2001,2002	1998	2002	1998	1999	2000	2001	2002
5	2001-2003	2001,2002,2003	2001	2003	2001	2002	2003

B1=SUBSTITUTE(TRIM(E1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1)," ",",")
C1=IFERROR(--LEFT(A1,4),"")
D1=IFERROR(--RIGHT(A1,4),"")
E1=C1
F1=IFERROR(IF(E1+1>$D1,"",E1+1),"")....copied across as needed

Then copy that whole mess down as far as you need. Then hide columns C:??

Thank you for your valuable time. I think there was a misunderstanding or maybe i did not understating so well.

So here is the range of years I currently have (Example)

[Column A]
2010-2015
2008-2012
2011-2015
1998-2002
2001-2003

I need to convert Column A into the Column B

[Column B]
2010, 2011, 2012, 2013, 2013, 2015
2008, 2009, 2010, 2011, 2012
2011, 2012, 2013, 2014, 2015
1998, 1999, 2001, 2002
2001, 2002, 2003

Is there a way I can attach the file so you can have a better idea

Thanks

[FDibbins]

That is what I did. If you look at column B, this gives you what you said you wanted.

[dflak]

To use the UDF in cell B2 for example =SplitYears(A2)

[Czeslaw]

In cell C1 to put a formula and drag to the right and down.

Please Login or Register  to view this content.

[benvillacrez]

That is what I did. If you look at column B, this gives you what you said you wanted.

Thank you it works, but the only issue is that i have over 5000 set of years that i need to arrange and they all vary their START and END year which mean I will have adjust the formula manually for it to work

[benvillacrez]

In cell C1 to put a formula and drag to the right and down.

Please Login or Register  to view this content.

Thank you for your time. I also tried this and it only gives me the first starting year of the column

[benvillacrez]

To use the UDF in cell B2 for example =SplitYears(A2)

Thank you so much for your valuable time I also tried this and when I apply the UDF it does not work it gives me this error #NAME?

Would it be easier if i have the starting YEAR and END in two different columns? Because I have them in that format as well. The years can be in different columns as long I get the sequence of years withing the START year and END year.

I'm attaching the years. One file is with the START and END in one column. The other file is with the START and END year in different columns.

[Czeslaw]

Attached is an example.

[benvillacrez]

Attached is an example.

Thank you so much this one seem to work perfectly. Thank you so much once again!!!