=(COUNTIF(A1,"*ABC*")0)*TRIM(RIGHT(SUBSTITUTE(A1 ," ",REPT(" ",99)),99))

No need to test for 0. COUNTIF will return 1 or 0.

1*TRIM(...) = the number
0*TRIM(...) = 0

Assuming the extracted string is always a number:

=COUNTIF(A1,"*ABC*")*TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

You can even remove the TRIM function and it'll work. But, not knowing the
full extent of possible data entries I'd still leave it in the formula.

=COUNTIF(A1,"*ABC*")*RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Try this:

=(COUNTIF(A1,"*ABC*")0)*TRIM(RIGHT(SUBSTITUTE(A1, " ",REPT(" ",99)),99))


"Dinesh" wrote:

Hi,

I have three slightly three different text string where I want to extract
a
proceed value (672707.58) from it. Below are the text strings.

1) XYZ Corp MLA 2799 SCH 053-000 ABC-72494 Proceeds to ABC for -
672707.58
2) XYZ Corp MLA 2799 SCH 053-000 ABC-72494 Proceeds to ABC Inc for
672707.58
3) XYZ Corp MLA 2799 SCH 053-000 ABC-72494 Proceeds to ABC Inc 672707.58

Below is a formual that works only on the first scenerio. For the second
and
third text string, I get a "#value" error.

=IF(ISERROR(SEARCH("ABC",C19)),"0",MID(C19,SEARCH( "- ",C19)+1,99)+0)

Thanks,

Dinesh