You interested in a macro solution?

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9,.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord Dibben MS Excel MVP

On Fri, 1 Jun 2007 14:41:01 -0700, Jan wrote:

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