How do you parse a substring from an Excel cell?

Question

I have a column with the following values:

month
201201     // means  January of 2012
201102     // means February of 2011
201203
201304
...
201307
201106

I need to create a new column that would be convert the last two numbers in the month column into the name of the month, so that 201201 will become Jan-2012, 201106 should become Jun-2011.

How can I parse the last two characters?

Answer 1

This gives you the formatting you want:

=TEXT(DATE(LEFT(A1,4),RIGHT(A1,2),1),"MMM-YYYY")

Answer 2

=DATE(MID(A2,1,4),MID(A2,5,2),1)

Answer 3

You can use LEFT and RIGHT functions and concatenate the results. Assuming the first value is in A2:

=(1&"-"&RIGHT(A2,2)&"-"&LEFT(A2,4))*1

And format the cell as mmm-yyyy.

This assumes that your regional settings have dates as dd/mm/yyyy format.

Otherwise, you'll have to switch the month and date around.

=(RIGHT(A2,2)&"-"&1&"-"&LEFT(A2,4))*1