Formula to extract date from a string as MMM-YY format

[kaushik03]

Hi All,

I have text string as follows in column A (cell A1 and A2):

standard_units_mth_01_2013
standard_units_mth_02_2013

What I want in col B (in cell B1 and B2) are:

Jan-13
Feb-13

Could you please get me the formula for the same?

Regards,
Kaushik

 

[Mike H..]

Hi All,

I have text string as follows in column A (cell A1 and A2):

standard_units_mth_01_2013
standard_units_mth_02_2013

What I want in col B (in cell B1 and B2) are:

Jan-13
Feb-13

Could you please get me the formula for the same?

Regards,
Kaushik

Hi,

Assuming your first string is in A1, try this. It will extract the date as an un-formatted number so format to your choice.


=LOOKUP(1E+100,--MID(SUBSTITUTE(A1,"_","/"),SMALL(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,"_","/")&"0123456789"),1),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,"_","/"))))))

 

[Khalid NGO]

Hi Kaushik,

If your string pattern is identical, you can use the following too:

=DATEVALUE(1&SUBSTITUTE(RIGHT(A1,FIND("_",A1)-1),"_","/"))

Regards,

 

[Deepak]

If the pattern is identical as said Khalid NGO then why not this...

=TEXT(DATEVALUE(1&SUBSTITUTE(RIGHT(A1,8),"_","/")),"MMM-YY")

 

[kaushik03]

Hi guys!!!

Thanks all for your help.

@Mike: It works like a charm!!! Hats off to you man!! May I please request you to explain me (with a short write up) how the formula works.

Looking forward to your explanation..

Many thanks dude!!

 

[Mike H..]

Hi guys!!!

Thanks all for your help.

@Mike: It works like a charm!!! Hats off to you man!! May I please request you to explain me (with a short write up) how the formula works.

Looking forward to your explanation..

Many thanks dude!!

Hi,

Of course I'll explain but to make things simple we can use the same formula but simplified a bit. In your sting the were several underscores and the original formula use the SUBSTITUE function to replace these with the / character. The reason for this is that Excel wouldn't have recognised 01_2013 as a date but would recognise 01/2013. let's assume your original string already had the / character like this

standard/units/mth/01/2013

and the simpler formula becomes this. Now all I've done here is replace this

SUBSTITUTE(A1,"_","/")

with the simple reference to A1 in 3 places

=LOOKUP(1E+100,--MID(A1,SMALL(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"),1),ROW(INDIRECT("1:"&LEN(A1)))))

The formula works by returning an ARRAY of text strings and the first thing it does is this bold bit finds the first number in the string

=LOOKUP(1E+100,--MID(A1,SMALL(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"),1),ROW(INDIRECT("1:"&LEN(A1)))))

When that evaluates it looks like this

=LOOKUP(1E+100,--MID(A1,SMALL({20,21,23,26,32,33,34,35,36,37},1),ROW(INDIRECT("1:"&LEN(A1)))))

The only number we're interested in is the 20 which is the position of the first number, the zero so the SMALL bit evaluates next like this to find the 20

=LOOKUP(1E+100,--MID(A1,20,ROW(INDIRECT("1:"&LEN(A1)))))

What happens now is the ROW function evaluates and produces a range of numbers from 1 to the number of characters in the cell like this

=LOOKUP(1E+100,--MID(A1,20,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27}))

Now we're getting close and the MID function evaluates and produces an array of 27 strings like this

=LOOKUP(1E+100,--{"0";"01";"01/";"01/2";"01/20";"01/201";"01/2013";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
";"01/2013
"})

Now a lot of the strings are the same because we only have 7 characters after the zero but that doesn't matter because as you can see we already have our date, all we have to do is capture it so the -- part evaluates next and this coerces all those text string into numbers.

=LOOKUP(1E+100,{0;1;#VALUE!;42036;43831;#VALUE!;41275;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})

Now the LOOKUP bit evaluates and the value we're looking up is a very large number which we can be pretty sure it won't find and if lookup can't find what it's looking for then it returns the last value that is less than the lookup value which in this case is 41275, Excel has recognised 01/2013 as January 2013 and because there was no day Excel add a 1 and returned the 41275.

A bit long winded but I hope this helps.