On Wed, 9 Mar 2005 23:00:25 +0100, "gilgil" wrote:

on the Web in Excel

1.90 ------------------ 0,10417
2.00 ------------------ 2.00 (with digit)
1.80 ------------------ 0,09722

Thanks.

--
gilgil

Thank you. I see what is happening now. Your system is interpreting the
decimal as a time separator. So 1.90 gets interpreted as 1 hr 90 minutes and
0,10417 represents 0230 (1 hr 90 min is the same as 2 hr 30 min). Excel stores
time as fractions of a day.

I know of no way to derive the original 1.90 from 0,10417 since other entries
could have resulted in the latter (e.g. 2.30 would give the same number).

Some suggestions:

1. Change your windows regional settings to a compatible version before doing
the import, then change them back.

2. If you are doing a copy/paste to import the data, you might try
preformatting as text. If that works, you could then insert a helper column;
use the SUBSTITUTE worksheet function (=--SUBSTITUTE(A1,".",","); copy/paste
values and delete the superfluous column. You may have to replace the comma
separators in the function arguments with whatever is appropriate for your
locale.

3. Preformat as text, and then run a macro on the data to do the substitution.

4. If preformatting as text does not work, then you will need a VB routine to
process the data before importing it into Excel.

For choice 3, the macro might be something like:

=====================
Sub foo()
Dim c As Range
Dim aoi As Range

Set aoi = Range("A1:A10")

For Each c In aoi
c.Value = Val(Replace(c.Value, ".", ","))
c.NumberFormat = "General"
Next c

End Sub
=======================


--ron