1

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

  • 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
5

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
  • 1
    @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
  • 1
    @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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.