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

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






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron Rosenfeld wrote :

On Tue, 05 Apr 2005 10:57:08 -0700, Darin Kramer
wrote:

The problem is it works FINE on my machine, but when looked at by
someone else, the . is changed to a , and the formulae produces a
#value error. Im going insane trying to get it to work in both
enviornments. Any thoughts on how to achieve a consistent solution
(ie works on both machines) would be welcomed.

Would a VBA solution be acceptable?

Check this out, but I think that VBA is US-centric. So it may
interpret input as US and give output in the local format.

For example, the simple UDF:

======================
Function GetVal(rg As Range) As Double
If IsNumeric(rg.Value) Then
GetVal = rg.Value
Else
GetVal = Val(rg)
End If
End Function

======================

given a cell containing 12.2a will return 12.2 with the regional
settings set to English(US) and will return 12,2 with the regional
settings set to Dutch(Belgium).

If this is OK, to enter the UDF, <alt-F11 opens the VB Editor.
Ensure your project is highlighted in the Project Explorer Window,
then Insert/Module and paste the above code into the window that
opens.

To use this, enter the formula

=getval(cell_ref)

into some cell where cell_ref is contains your value.

But be sure to check all the various possible contents of cell_ref
under the different regional settings to ensure you get the output
you desire.

For example, if you are using Dutch settings and have the text string
12.2a in cell_ref, the function will return the number 12,2 as you
desire. 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

So if the above scenario is possible, we may need a different
approach.



--ron