extracting negative numbers

**darkhangelsk** · 08-17-2009, 09:42 AM

hi! I want to ask help from you guys,

I used this code to extract the value on the text: "11/09/09 backyard $400"

=IF(ISNUMBER(SEARCH("back",A4)),RIGHT(A4,LEN(A4)-FIND("$",A4))+0,"")

and return "400" but i've encountered a value which is negative (could be
-$400, or ($400); and i got an error of #value, could anyone resolve this problem?

Thanks!

**contaminated** · 08-17-2009, 10:02 AM

Maybe this would help

Please Login or Register  to view this content.

**martindwilson** · 08-17-2009, 10:13 AM

assuming (400) means negative
=IF(AND(ISNUMBER(search("back",A4)),OR(ISNUMBER(FIND("-$",A4)),ISNUMBER(search("(*)",A4)))),RIGHT(SUBSTITUTE(SUBSTITUTE(A4,")",""),"(","$"),LEN(SUBSTITUTE(SUBSTITUTE(A4,")",""),"(","$"))-FIND("$",SUBSTITUTE(SUBSTITUTE(A4,")",""),"(","$")))*-1,IF(ISNUMBER(SEARCH("back",A4)),RIGHT(A4,LEN(A4)-FIND("$",A4))+0,""))

**darkhangelsk** · 08-17-2009, 10:14 AM

it's working but how about my first coding? can i use

=IF(ISNUMBER(SEARCH("iNSP",A29)),RIGHT(A29,LEN(A29)-FIND("$",A29))+0,"")&if(--MID(SUBSTITUTE(TRIM(A31)," ","@",LEN(A31)-LEN(SUBSTITUTE(A31," ",""))),FIND("@",SUBSTITUTE(TRIM(A31)," ",)...

**daddylonglegs** · 08-17-2009, 10:21 AM

Try

=IF(ISNUMBER(SEARCH("back",A4)),TRIM(RIGHT(SUBSTITUTE(A4," ",REPT(" ",9)),9))+0,"")

Note: the above works if your default currency is $, if not then amend to

=IF(ISNUMBER(SEARCH("back",A4)),SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A4," ",REPT(" ",9)),9)),"$","")+0,"")

**darkhangelsk** · 08-17-2009, 10:24 AM

Gosh! Martin again to save the day!

Thanks to both of you, i know the codings were very messy at this time, but still very useful... Thanks!!!

but wait...

daddy's code were working also, and it's look like very simple... Thanks for the three of you! Really appreciate it!