Ok, this assumes that the only time ( ) appears in the string is with
numbers enclosed:

=IF(AND(ISNUMBER(FIND({"(",")"},A1))),LOOKUP(10^10 ,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255")))),"")

Biff

"Jan" wrote in message
...
example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)

result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok

a) numbers could be anywhere in the text string however as they are not
between brackets ( ) they should be ignored.

hope this helps


"T. Valko" wrote:

Post *several representative samples* and tell us what the results for
each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary
is 3
positions. could also be 5 uur. the only common part is that the
data
is
ALWAYS at the right hand site of the text string