Numbers vs Values

[Fredrik Wahlgren]

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

 

[Darin Kramer]

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 ***

 

[Darin Kramer]

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 ***

 

[Jim Thomlinson]

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

 

[Fredrik Wahlgren]

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

 

[Jim Thomlinson]

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

 

[Ron Rosenfeld]

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

 

[keepITcool]

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 :

 

[Darin Kramer]

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

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

 

[Ron Rosenfeld]

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