EXCEL: FORMAT a "jan" "feb" "mar" "apr" into a MONTH ( 01,02,03)

Question

Please anybody know:

Date----------------Result--------------------------------------What I want!
9.99 LATE AUG 13 ---IF(ISNUMBER(SEARCH("aug",B2&" "&C2)),"08"---08
9.99 Early Sept 13 ----"""--------------------------------------09

---

if i use for all 12mnths then its gives error:-the specified formula cannot be entered because it uses more levels of nesting than are allowed

for eg:-

  (B2)------------------(C2)

HK-753F(6)LATE AUG 13------- MERC

HK-754F(6) ------------------------ EARLY SEPT 13

11.99 -------------------------------- LATE SEPT 13

11.99 -------------------------------- LATE SEPT 13

10.99 JUNE 13 --------------------- MERC

Answer 1

Try this formula

=MATCH(2^15,SEARCH({"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},B2&" "&C2))

or a slightly shorter but less transparent version....

=MATCH(2^15,SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm"),B2&" "&C2))

Explanation

When you do a SEARCH for an "array" of terms like {"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"} then result is also an array, a #VALUE! error for each term that doesn't exist in B2&" "&C2 and a number (the position) if it does, so if B2&" "&C2 = HK-753F(6)LATE AUG 13 MERC then the result of

=SEARCH({"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},B2&" "&C2)

is the following array:

{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,17,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

Note that all entries are errors except for 17 at position 8 (17 is the position of the "A" of "AUG" in B2&" "&C2).

When MATCH function (with no 3rd argument) looks up a "bignum" like 2^15 in that array it always matches with the last number so the result is 8 (because "aug" is 8th in the list of months)