Re: Parse String
From: Harlan Grove (hrlngrv_at_aol.com)
Date: 02/03/05
- Next message: Dave Peterson: "Re: data validation quirk"
- Previous message: jcmcmahon: "RE: Visual Basic Error"
- In reply to: Myrna Larson: "Re: Parse String"
- Messages sorted by: [ date ] [ thread ]
Date: 2 Feb 2005 18:02:27 -0800
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
- Next message: Dave Peterson: "Re: data validation quirk"
- Previous message: jcmcmahon: "RE: Visual Basic Error"
- In reply to: Myrna Larson: "Re: Parse String"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
