"Darin Kramer" wrote in message
...


I have a spreadsheet in which numbers are formatted to numbers. Within
the "numbers" there are some letters, eq question 12.2a. For various
reasons I use the Left(a1,4) command to just get the 12.2. I then
multiply it by 1 to convert the result to a number, which i then apply a
Vlookup to. (formuale is thus (1*left(a1,4)
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.
(Elsewhere in the spreadsheet, where I have had to check if a value
equals another one, I have said =12+2/10 to get back to a 12.2 (but not
12,2)- which may be also be a problem?
Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***

Open the Control Panel and then Regional Options. Check decimal Symbol.

/Fredrik

I have a spreadsheet in which numbers are formatted to numbers. Within
the "numbers" there are some letters, eq question 12.2a. For various
reasons I use the Left(a1,4) command to just get the 12.2. I then
multiply it by 1 to convert the result to a number, which i then apply a
Vlookup to. (formuale is thus (1*left(a1,4)
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.
(Elsewhere in the spreadsheet, where I have had to check if a value
equals another one, I have said =12+2/10 to get back to a 12.2 (but not
12,2)- which may be also be a problem?
Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***

Frederick, every user has different regional settings, and I cant do
that for all of them that will use the application. It needs to work in
all environments.



*** Sent via Developersdex http://www.developersdex.com ***

Lets see if I can do a little better on this than I did on your last. Try
this formula to get the value

=VALUE(LEFT(A1,2)+(VALUE(MID(A1,4,1)))/10)

Where 12.2a or 12,2a is in cell a1

HTH

"Darin Kramer" wrote:



I have a spreadsheet in which numbers are formatted to numbers. Within
the "numbers" there are some letters, eq question 12.2a. For various
reasons I use the Left(a1,4) command to just get the 12.2. I then
multiply it by 1 to convert the result to a number, which i then apply a
Vlookup to. (formuale is thus (1*left(a1,4)
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.
(Elsewhere in the spreadsheet, where I have had to check if a value
equals another one, I have said =12+2/10 to get back to a 12.2 (but not
12,2)- which may be also be a problem?
Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***

"Jim Thomlinson" wrote in message
...
Lets see if I can do a little better on this than I did on your last. Try
this formula to get the value

=VALUE(LEFT(A1,2)+(VALUE(MID(A1,4,1)))/10)

Where 12.2a or 12,2a is in cell a1

HTH


Yes that is excellent assuming there are no entries like 6.25a
/fredrik

True I was assuming ##?#. If #?## exists then we need to add an if statement
similar to

=IF(ISERROR(VALUE(MID(A2,2,1))), VALUE(LEFT(A2,1)+(VALUE(MID(A2,3,2)))/100),
VALUE(LEFT(A2,2)+(VALUE(MID(A2,4,1)))/10))

HTH

"Fredrik Wahlgren" wrote:


"Jim Thomlinson" wrote in message
...
Lets see if I can do a little better on this than I did on your last. Try
this formula to get the value

=VALUE(LEFT(A1,2)+(VALUE(MID(A1,4,1)))/10)

Where 12.2a or 12,2a is in cell a1

HTH


Yes that is excellent assuming there are no entries like 6.25a
/fredrik

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

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

Thank you all so much!!!!!!!!! It works!!!!!!!!!!!!!!!!!!!!

*** Sent via Developersdex http://www.developersdex.com ***

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