extracting numbers from the string

[essai2008]

I wanted to know if anyone could provide me with the forumula to obtain the value located to the right of the string. Values can certainly change at all times, but is there a way to catch the numeric value after "book value". The right function is not very efficient because transfers can range from $1 - $1,000,000



ALLANA POTASH CORP NESBITT BURNS INC.TFSA TRANSFER BOOK VALUE 5761.95

Thanks in advanced

[martindwilson]

=right(substitute(a1," ",rept(" ",25)),20)+0

[FDibbins]

If you will always have VALUE at the end of the text, try this...
=--MID(A1,SEARCH("value",A1,1)+6,99)

If that will not always be the case, but the value will always be at the end of the string, with a space in front of it, try this...
=--MID(A1,SEARCH("xx",SUBSTITUTE(A1," ","xx",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1)+1,99)

[essai2008]

Thank you for your immediate response,

You guys are always very helpful
Cheers

[Tony Valko]

The right function is not very efficient because transfers can range from $1 - $1,000,000

ALLANA POTASH CORP NESBITT BURNS INC.TFSA TRANSFER BOOK VALUE 5761.95

Here's another one...

=LOOKUP(1E100,--RIGHT(A2,ROW(INDIRECT("1:11"))))