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

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

• What is the format of the other cells you have? And why did you concatenate B2 and C2? – Jerry Sep 2 '13 at 14:42
• its occurrence possible in B2 and C2. other cell format given question – John Sep 2 '13 at 14:46
• Okay. Is it always 9.99 Late or 9.99 Early? And when you say it can be in B2 and C2, can there be text in both? – Jerry Sep 2 '13 at 14:49
• no sometimes its look like HK-753F(6) LATE AUG 13 both(B2 and C2) have text but "jan" "feb" is only in one cell both of them. – John Sep 2 '13 at 14:53
• @Sandy Edit your post and give us some decent examples which include all your cases, instead of this back-and forth commenting. – Jan Doggen Sep 2 '13 at 15:08

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)

• Thanks it works but i need "01" "02" format not a "1","2". have you any resolve point for first one formula. – John Sep 2 '13 at 16:01
• @saNdy You can wrap it around a TEXT() like this: =TEXT(MATCH(2^15,SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm"),B2&" "&C2)),"00") – Jerry Sep 2 '13 at 16:04
• =MATCH(2^15,SEARCH({"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},B2&" "&C2)) in that one please. – John Sep 2 '13 at 16:06
• @saNdy It's basically the same thing: =TEXT(MATCH(2^15,SEARCH({"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},B2&" "&C2)),"00") – Jerry Sep 2 '13 at 16:11
• Thanks @jerry you are very helpful for me in that issue. Actually am the beginner in that. Thanks again. – John Sep 2 '13 at 16:19