Would definitely like to come back to this one and improve my tool usage - I feel like both my formula and splitting of the data could be improved.
So I first start with a text to columns tool to split the data by a delimiter of the letter J, as this is the only case where there was a number.
This produced an output like so:
Then i wanted to remove all the numbers from the Date Column, so I would just have the letters. The data cleansing tool was great for this.
Then I didn't need the middle column, so I removed that and renamed the year column:
Now I was a little bit stuck, as i knew i'd need to run some kind of loop/formula on the data to apply the previous years to column, to fill out the nulls. I did have to look up how to use the previous row, but the logic for applying it was fairly fundamental, if the row is null, apply the last year. If its not null, leave it!
Then originally i applied a nice Switch/Case statement (which i love), but sadly due to the conditions that follow January/June/July (all with the same starting letter) this didn't work!
This stage provided me with this data:
In which i just had to remove the date column, and then bring back in the original data column as the output required
I did this in a super convoluted manner. Two multirows to get the Year and a month num. 1-12 for each year. Then a formula tool to get the month string in two digit format. Then a fake_date in order to use the "%b" format method to get the Month Name as Jan Feb etc. Then a select and rinse out the unnecessary fields and rename the remaining to the desired format.