extract numbers with specific text from right or left

**darkhangelsk** · 08-15-2009, 02:21 PM

wew, i'm doing our payoff right now, (dat's why doing some overtym in our home) and encounter this problem of mine,

i use this code to get the value from the cell that contains "Ink"., and i got the codes from reading other problems:

=IF(SEARCH("Ink",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

like this in a1 -> Ink 253.00

and totally working! but the problem is if the word "ink" in the left of the value --> 253.00 ink and the result is #NA, is there any way that i can get the value whether the word Ink is in the left side or right side of the value?

also bothered why is it if the word is not "ink" in the cell and return -> #value since i put ("") in the last part of If function(value if false)?

**martindwilson** · 08-15-2009, 02:32 PM

=IF(ISNUMBER(SEARCH("Ink",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,"")
works either way for me.

**darkhangelsk** · 08-15-2009, 02:38 PM

so the missing link is the "isnumber", i also found out that i can use iferror function if i dont want to see the #value.

Again Martin, you're the best!

Thanks!!