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?

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?

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?


.

Sorry, forgot to finish it. Another shot

=MID(B1,FIND(" f",B1,FIND(",",B1))+2,FIND(" ",B1,FIND("
f",B1,FIND(",",B1))+1)-FIND(" f",B1,FIND(",",B1))-2)

--

HTH

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


"Geoff Murley" wrote in message
...
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?


.

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?


.

Thank you both for your help. The VBA code is superb.

-----Original Message-----
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?


.


.

Myrna Larson wrote...
This formula returns 450 given the text you specified.

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

Chokes if 'f#..#' appears at the end of A1.

The general case in which there could be any number of 'f' substrings
before the leftmost 'f' immediately followed by a numeral and the
'f#..#' substring could be delimited by any other characters as well as
appearing at the end of the string would be the array formula

=MID(A1,MIN(FIND("f"&{0;1;2;3;4;5;6;7;8;9},A1&"f0f 1f2f3f4f5f6f7f8f9f0"))+1,
MATCH(0,-ISNUMBER(-MID(A1&"
",ROW(INDIRECT((MIN(FIND("f"&{0;1;2;3;4;5;6;7;8;9} ,
A1&"f0f1f2f3f4f5f6f7f8f9f0"))+2)&":"&(LEN(A1)+1))) ,1)),0))


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.

Then it'll return '.-.' for "123 foobar lane -.f.-.f.- !! f10"

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
....

If you're going to resort to VBA, why not make it general? For string
parsing nothing beats regular expressions. Using the Subst function in

http://groups-beta.google.com/group/...d252b4201d9d22

(or http://makeashorterlink.com/?L2BA2136A ). Then use the worksheet
formula

=subst(A1,".*?f(-?\d*\.?\d+).*","$1")

which will only pull true numeric substrings following 'f' rather than
any stray sequences of hyphens and periods that happen to follow an
'f'. For instance,

=subst("123 foo foo-foo f--2--f f-.547abcdef
f0",".*?f(-?\d*\.?\d+).*","$1")

returns -.547. Try, just try, to handle this kind of string with
reasonable numeric substring semantics without regular expressions. Of
course it can be done, but it requires a state machine.

True. Given his example (an address?), I inferred it would be followed by a
space and would not occur at the end of the string.

If it is normally followed by a space, this modification, that ensures there
is a space at the end of the text, would handle that variation.

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

But it assumes his statement that there is only 1 "f" is correct.

On 2 Feb 2005 13:18:37 -0800, "Harlan Grove" wrote:

Myrna Larson wrote...
This formula returns 450 given the text you specified.

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

Chokes if 'f#..#' appears at the end of A1.

The general case in which there could be any number of 'f' substrings
before the leftmost 'f' immediately followed by a numeral and the
'f#..#' substring could be delimited by any other characters as well as
appearing at the end of the string would be the array formula

=MID(A1,MIN(FIND("f"&{0;1;2;3;4;5;6;7;8;9},A1&"f0 f1f2f3f4f5f6f7f8f9f0"))+1,
MATCH(0,-ISNUMBER(-MID(A1&"
",ROW(INDIRECT((MIN(FIND("f"&{0;1;2;3;4;5;6;7;8;9 },
A1&"f0f1f2f3f4f5f6f7f8f9f0"))+2)&":"&(LEN(A1)+1)) ),1)),0))


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.

Then it'll return '.-.' for "123 foobar lane -.f.-.f.- !! f10"

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
...

If you're going to resort to VBA, why not make it general? For string
parsing nothing beats regular expressions. Using the Subst function in

http://groups-beta.google.com/group/...d252b4201d9d22

(or http://makeashorterlink.com/?L2BA2136A ). Then use the worksheet
formula

=subst(A1,".*?f(-?\d*\.?\d+).*","$1")

which will only pull true numeric substrings following 'f' rather than
any stray sequences of hyphens and periods that happen to follow an
'f'. For instance,

=subst("123 foo foo-foo f--2--f f-.547abcdef
f0",".*?f(-?\d*\.?\d+).*","$1")

returns -.547. Try, just try, to handle this kind of string with
reasonable numeric substring semantics without regular expressions. Of
course it can be done, but it requires a state machine.