extracting values in a specific text

Sigh, , I found some bug in my worksheet, and i think you people were the only one who can help me with this..


This how it looks like, i used this code (see below) to get the amount if there is a word "ins" (martin's code)




IF(AND(ISNUMBER(SEARCH("ins",B47)),NOT(ISNUMBER(SEARCH("insp",B47)))),LOOKUP(99^99,--("0"&MID(B47,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B47&"0123456789")),ROW($1:$10010))))+0,""))


it works pretty great, but when things getting better, i've encounter this data: 12,300 ins 09-10 flood

so what i did, is to put another if function, like this:



=IF(AND(ISNUMBER(SEARCH("flood",B47))),"",IF(AND(ISNUMBER(SEARCH("ins",B47)),NOT(ISNUMBER(SEARCH("insp",B47)))),LOOKUP(99^99,--("0"&MID(B47,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B47&"0123456789")),ROW($1:$10010))))+0,""))

and cool! it's working! but.. again another bad data i've encounter ->>> "12,300 ins flood, fire" also "12,300 ins flood, fire, wind".


and now, my head is spinning around, i don't know what to do.. help me guys..

Thanks...

We are not familiar with the original requirements and your current dilema... can you please elaborate as if it were a new topic....

i just realised its easier to do it like this
=IF(OR(ISNUMBER(SEARCH({"flood","fire","water","insp"},B47))),"",IF(ISNUMBER(SEARCH("ins",B47)),LOOKUP(99^99,--("0"&MID(B47,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B47&"0123456789")),ROW($1:$10010))))+0,""))
just add any words that may have "ins" associated with them to
{"flood","fire","water","insp"}

post your latest sheet ,but i see on posting its put a space in the middle of posting its put a space in LOOKUP i.e. LOOK UP get rid of that space