Myrna Larson wrote...
I just re-read his original message, where he gives the example
(f450). The
formula won't handle that -- it requires a space at the end of the
number.

*Your* formula. Mine handles anything matching the regexp 'f\d+'.

The point is that numeric substrings are well defined - they end with
the rightmost numeral. Whatever follows, if anything, is irrelevant.
Only the transition matters. Therefore, it's sufficient to find all
numerals following the 'f' rather than finding anything in particular
after the rightmost numeral.

I think the macro handles all possibilities, however.

Your udf does check for 'f' followed by a numeral, so I was wrong about
it picking up substrings beginning with 'f' followed by hyphens, commas
or periods, but it does include trailing punctuation, so it could pick
up trailing hyphens, commans and periods that would prevent the
substring from being converted to a number. For example, in both

123 My Street, Wherever f999.. whatever

123 My Street, Wherever .f9.9. whatever

your udf chokes on the CDbl call. If you want to avoid regular
expressions, you could build the state machine into your subsequent
character test.


Function gnaf(s As String) As Variant
Dim p As Long, q As Long, vc As String, c As String * 1

gnaf = CVErr(xlErrValue)
vc = ".0123456789,-"

Do
p = InStr(p + 1, s, "f")
If p = 0 Then Exit Do

If Mid(s, p, 2) Like "f#" Then
p = p + 1
q = p + 1

Do While q <= Len(s)
c = Mid(s, q, 1)

If InStr(1, vc, c) = 0 Then Exit Do 'inner Do

q = q + 1

If c = "-" Then
Exit Do 'inner Do

ElseIf c = "." Then
vc = Mid(vc, 2)

End If

Loop

gnaf = CDbl(Mid(s, p, q - p))
Exit Function

End If

Loop

End Function