Hey spreadsheet wizards!
I’m stuck on a tricky problem. I need to make a formula that can list years vertically based on what’s in another cell. Here’s what I mean:
If a cell has:
2001-2003
2004-2005
2006
I want the output to be:
2001
2002
2003
2004
2005
2006
It should work for single years too. So if the input is just ‘2007’, the output should be ‘2007’.
I’ve been banging my head against this for hours! Any ideas on how to do this in Google Sheets? If it’s not doable there, I can use Excel instead.
Thanks a bunch for any help you can give!
As someone who’s worked extensively with spreadsheets for data analysis, I can assure you that solving this problem is very feasible in both Google Sheets and Excel.
One effective approach is to split your input string into start and end years, then use a function such as SEQUENCE to generate the complete range between the minimum and maximum values. You can further refine the output using functions like FILTER or QUERY to ensure that only the relevant year sequence is displayed.
This method has consistently delivered good results in my experience, and I hope it helps you resolve the issue efficiently.
yo dude, i got u covered. try this formula:
=ARRAYFORMULA(SEQUENCE(MAX(SPLIT(FLATTEN(A1:A), "-"))-MIN(SPLIT(FLATTEN(A1:A), "-"))+1, 1, MIN(SPLIT(FLATTEN(A1:A), "-"))))
it’s a bit messy but it’ll do the job. just replace A1:A with ur range. lmk if u need any help!
I have encountered similar challenges in my work with financial data. In my experience, a good approach is to first separate the input into its start and end years, then use a function like SEQUENCE to generate the complete range from the lower to the higher year. Next, a function such as FILTER can be applied to ensure that only the relevant years are displayed. The exact formula might vary depending on the specific layout of your spreadsheet. This method works effectively in both Google Sheets and Excel and has proven to be reliable in many real-world scenarios.