Splitting Year

[Jesan]

Hello,

Good day.

I would like to ask if there's a way wherein I can split this kind of year 2009-2015 into each year?

2009
2010
2011
2012
2013
2014
2015

Thank you in advance.

Yours,
Jesan

 

[awoohaw]

You want to split the text 2009-2015 into 2009, 2010,2011,2012...2015?
which version of excel do you have?
If you have 365or2021:
=LET(
st, A1,
ld, 1* LEFT(A1,4),
rd, 1* RIGHT(A1,4),
SEQUENCE(1,rd-ld+1,ld,1))

 

[awoohaw]

WorkBook1.xlsx
	A	B	C	D	E	F	G	H
1	2009-2015
2	2009	2010	2011	2012	2013	2014	2015
3
Sheet3

Cell Formulas
Range	Formula
A2:G2	A2	=LET( st, A1, ld, 1* LEFT(A1,4), rd, 1* RIGHT(A1,4), SEQUENCE(1,rd-ld+1,ld,1))
Dynamic array formulas.

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[Column1.1]..[Column1.2]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Custom"})
in
    #"Removed Other Columns"

Book12
	A	B	C
1	Column1		Custom
2	2009-2015		2009
3			2010
4			2011
5			2012
6			2013
7			2014
8			2015
Sheet1

 

[Peter_SSs]

Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Simpler version of the earlier formula suggestion, assuming you have the SEQUENCE function

23 02 14.xlsm
	A
1	2009-2015
2	2009
3	2010
4	2011
5	2012
6	2013
7	2014
8	2015
9
SEQ

Cell Formulas
Range	Formula
A2:A8	A2	=SEQUENCE(RIGHT(A1,4)-LEFT(A1,4)+1,,LEFT(A1,4))
Dynamic array formulas.

If you do not have SEQUENCE, then

23 02 14.xlsm
	A
1	2009-2015
2	2009
3	2010
4	2011
5	2012
6	2013
7	2014
8	2015
9
10
SEQ (2)

Cell Formulas
Range	Formula
A2:A10	A2	=IF(IFNA(LOOKUP(9999,A$1:A1),0)-RIGHT(A$1,4)=0,"",LEFT(A$1,4)+ROWS(A$2:A2)-1)