Convert various string dates to a Year and Month column

[klikgevoel]

Hi all,

I've got a table in which there is a column known as Period. It describes the year and month as, for example, 1998MM02. In addition, it also has the row 1998JJ00, which in this case is the overall year accumulation. A brief example below:

Period
1998MM01
1998MM02
1998MM03
1998MM04
1998MM05
1998MM06
1998MM07
1998MM08
1998MM09
1998MM10
1998MM11
1998MM12
1998JJ00

What I want is two separate columns, one that contains the Year and another containing the Month.  So far, I managed to remove the characters from the Period column, and managed to separate it into two additional columns Year and Month. However, the year accumulation (i.e. 1998JJ00) is in both columns NaN ( - ). 

What is a simple solution to get the output given below ?

Period              Year            Month
1998MM01            1998            01
1998MM02            1998            02
1998MM03            1998            03
1998MM04            1998            04
1998MM05            .....           ...
1998MM06
1998MM07
1998MM08
1998MM09
1998MM10
1998MM11
1998MM12            1998          12
1998JJ00            1998          00

Thanks in advance!

[Shubham_Deshmukh · Accepted solution]

For Month : =Right(Period,'2')
For Year : =left(Period,4) OR subField(Period,'MM')