extracting numbers with the same word in diferrent position

[darkhangelsk]

wahhhh! need help from you guys, almost done with my new payoff worksheet and then this small bug appears and my excitement like "poof"(gone)!.



with this code --> =if(search("ins",i27,),LOOKUP(99^99,--("0"&MID(I27,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},I27&"0123456789")),ROW($1:$10000))))+0

i can get the value if the word contains "ins" like this --> "Ins 723.00" return 723.00 which is correct, and then i've encountered a word which is like"723.00 Ins", and martin gave me the code to get from both sides which is this one --> =IF(ISNUMBER(SEARCH("Ins",A1)),LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))+0,"") . Work perfectly. and my big problem is that the word Ins 723.00 means Insurance and the 723.00 means Inspection, so it means they're different. and it means I cant use martin's code with this.

So is there any way i can get only the value if the word Ins is in the right side of the amount and also the code if the value is in the left side of the amount. I need two formula for this. Thanks guys!!

[martindwilson]

give a few sample strings and what you want from each string

[darkhangelsk]

Okay, this is the sample...

[martindwilson]

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

[darkhangelsk]

and how come you know this and i dont?, (just kidding!).. Thanks Martin! you're an angel!








PS
wew, i'm giving you something, but it says try to spread it first, lolz...