Return a comment based on values in another cell

[Admin]

Assume your Invoice number in A2, in B2

=TEXT(TEXT(--("20"&MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01 23456789")),4)&"01"),"0000\/00\/00")+0,"mmm yy")

[Rick Rothstein]

Assume your Invoice number in A2, in B2

=TEXT(TEXT(--("20"&MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01 23456789")),4)&"01"),"0000\/00\/00")+0,"mmm yy")

Here is a similar formula that is eight characters and one fuction call less than the formula you posted..

=TEXT(0+(20&REPLACE(MID(A1,MIN(FIND({0,1,2,3,4,5,6 ,7,8,9},A1&"0123456789")),4),3,0,"-")&"-01"),"mmm yy")

I would note, though, that one could put this even shorter formula in the cell (saves another 15 characters and one more function call)...

=0+(20&REPLACE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8, 9},A1&"0123456789")),4),3,0,"-")&"-01")

and simply Custom Format the cells using "mmm yy" (without the quote marks, of course)... doing it that way, the cells will contain real dates, instead of Text that looks like a date, and which can be used directly in other formulas if need be.