Excel: Check whether a cell has text in another cell and with symbol, or trim left and right

Question

So i have cell from long text from formula witch

=RIGHT(J2;15)

2016-07-22 2016

How to take out both 2016- and 2016 using find? It needs to be variable since in 2017 it wont be 2016. Can i find using something like FIND(K2&"-";J2)? Example below.

Result i need to get only 07

I used

=MID(LEFT(J2;FIND(N2;J2)-5);FIND(K2;J2)+5;LEN(J2))

But it finds both of 2016 and gives nothing

Wanted to something like

=MID(LEFT(J2;FIND(N2;J2)-5);FIND(K2&"-";J2)+5;LEN(J2))

But doesnt work

Answer 1

Pulling the left 10 characters off the text string should allow direct conversion to a true date using the DATEVALUE function (possibly assuming the conputer's correct system DMY vs. MDY regional setting). Once that true date has been achieved, a wrapping TEXT function can resolve any part of the date (e.g. year, month or day).

=TEXT(DATEVALUE(LEFT(J2, 10)), "mm")      ◄ month as text representing a number
=--TEXT(DATEVALUE(LEFT(J2, 10)), "mm")    ◄ month as true number