On Tue, 05 Apr 2005 18:57:00 -0700, "keepITcool" wrote:

ron
12.1 and 12,1 will not give similar outcome with your udf.


It will if the inputs are both numeric, but not if text.

That's why I wrote:

"If the contents is the number 12,2 it will return 12,2 also. But if the
contents is the text string 12.2, the function will return the number 122,0"


i tried following and that seems to do the job, both for commas and
dots.


Function GetVal(rng As Range)
Dim vIn, sDec$

vIn = rng
sDec = Application.International(xlDecimalSeparator)

If sDec < "." And InStr(vIn, sDec) 0 Then
vIn = Application.Substitute(vIn, sDec, ".")
End If
GetVal = Val(vIn)

End Function

Both give somewhat inconsistent results depending on the input and regional
settings.

For example, given English(US) settings and all inputs as text:

Input Yours Mine
12.2a 12.2 12.2
12,2a 12 12
12.2 12.2 12.2
12,2 12 122

If the inputs are numeric, both should give accurate results.

That is why we need more information from the OP in order to come up with a
bulletproof scheme.


--ron