Most Generic "Un-format" ?? 1

[trids]

I'm looking for the most generic way of "unformatting" lots of incoming data that may have a variety of different descriptors prepended or appended to the critical numerical portion, without any knowledge of the actual format to undo.

For example ..

Val(sX$) fails when sX$ is ..
12,345.67 = 12
$100 = 0

Format(sX$, "##.##&quot fails when sX$ is ..
123.45ml = 123.45ml
8h = 8h

Val(Format(sX$, "##.##&quot) fails when sX$ is ..
12,345.67km = 12
$12,345.67 = 0 (unless the regional currency setting matches "$&quot

Any ideas .. ?

 

[sunaj]

val(replace(sX$,",","&quot)

would work for the examples that you've listed, but I'm sure that you could think some up that won't work (e.g. if there is letters in front of the number, that could be solved too of course)

Sunaj

 

[scorpio66]

If you are not averse to using the regular expression component you could try this:

Add a reference to Microsoft VBScript Regular Expressions

Private Function ToNumber(sValue as String) as Double
Dim r As RegExp, m As MatchCollection
Set r = New RegExp
r.Pattern = "[0-9.,]*"
Set m = r.Execute(sValue)
ToNumber = CDbl(m.Item(0).Value)
End Sub

Chaz

 

[scorpio66]

That should end with an End Function, not an End Sub

Sorry

Chaz

 

[scorpio66]

That'll teach me to not test before I post.

Private Function ToNumber(sValue As String) As Double
Dim r As RegExp, m As MatchCollection
Set r = New RegExp
r.Pattern = "[0-9\.,]+"
Set m = r.Execute(sValue)
ToNumber = CDbl(m.Item(0).Value)
End Function

Is the correct function. You might want to use the r.Test method to ensure that there is a valid number in sValue before calling .Execute.

Chaz

 

[trids]

Thanks folks !

I must get my head around Regular Expressions sometime. But I'll go with Sunaj's suggestion at this time.

Thanks again - you're all stars )