This formula returns 450 given the text you specified.

=MID(A1,FIND("f",A1)+1,FIND(" ",A1,FIND("f",A1))-1-FIND("f",A1))

The following VBA function will handle cases where there is more than one "f"
in the text. It finds the first f that is followed by a digit. Then it takes
all of the characters after that f that can be interpreted as a number, i.e.
it accepts a decimal point, comma, and minus sign as well as digits 0-9.

With an input of "myrna larson fabc def adc f1,280.48 zwz", the result is
1280.48


Option Explicit

Function GetNumber(sText As String) As Variant
Dim f As Long
Dim n As Long
Dim s As Long

GetNumber = CVErr(xlErrValue)
f = 0
Do
f = InStr(f + 1, sText, "f")
If f = 0 Then Exit Do

If Mid$(sText, f, 2) Like "f#" Then
f = f + 1
For s = f + 1 To Len(sText)
Select Case Asc(Mid$(sText, s, 1))
Case 44 To 46, 48 To 57 ' , - . 0-9

Case Else
Exit For
End Select
Next s
GetNumber = CDbl(Mid$(sText, f, s - f))
Exit Do
End If
Loop

End Function

On Wed, 2 Feb 2005 09:04:19 -0800, "Geoff Murley"
wrote:

Thanks Bob,

However it doesn't quite work. With my example I get
f450 my house abc

all I want is the '450' extracted.

Also there may not be a space or a comma before the 'f'.
So I could have myhouse(f450) fgh. I would only want the
450 in the next cell. The only thing I can assume is that
the letter 'f' will be followed by a number. So would a
bit of VBA be needed to check for a 'f' immediately
followed by a number?


-----Original Message-----
As long as there is a comma preceding bit, just one, and
a space before the
f, then

=MID(B1,FIND(" f",B1,FIND(",",B1))+1,99)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Geoff Murley"
wrote in message
...
I have a column containing Strings such as:

"10 new street, Wales f450 myhouse abc"

in each row.

I would like some code to extract the number after
the 'f'
character so that in this example '450' is written to
the
cell in the adjacent column. So if the above string was
in cell B1 , '450' would be written to C1 and so on for
all the entries in column B. Assume that there will
only
be one 'f' entry in each string.

Can anyone help please?


.