Join us at the Everyday AI Conference in London, New York & Bengaluru! REGISTER NOW

split years from one cell to multiple cell

jasonsnare
Level 1
split years from one cell to multiple cell

HI,

I have one dataset and it contains the year ranges in one cell (1993-1996). I want to split each year into separate cells (1993,1994,1995,1996). Does anyone have an Idea suggest me. 

2 Replies
Rushil09
Level 1
Level 1

Hi, 

How exactly will the cells  be laid out? in form of columns?

Can you sen the screenshot of what you want to do if i am getting this wrong.

AlexGo
Dataiker
Dataiker

If you want to use the visual recipe steps and you have a consistent format, you could do something like:

  1. Split the cell on '-',
  2. Use a formula such as "forRange(year_range_0,year_range_1, 1+1, v, v)"
  3. Unnest the array into separate columns

Screen Shot 2022-04-12 at 9.31.25 AM.png

This gets the first and last year, takes all years between the first and the last, steps through each year and then applies a formula to it (in this case the formula is just returning the value).

You might have issues if the format is inconsistent though e.g. there's a single year. You would have to edit the formula step to include an 'if' to check for things like this.

 

0 Kudos