VBA - How to change the decimal sign
 

Windows XP Home + Excel 2000

How to change decimal sign from "." to "," in importing text from web
tables?
The system setting in my computer for decimal sign is "," and not "."

Thanks.
--
gilgil

VBA - How to change the decimal sign
 

The decimal symbol is set he
Start | Settings | Control Panel | Regional Options. Have a look at the
Numbers tab.

"gilgil" wrote:

Windows XP Home + Excel 2000

How to change decimal sign from "." to "," in importing text from web
tables?
The system setting in my computer for decimal sign is "," and not "."

Thanks.
--
gilgil

VBA - How to change the decimal sign
 

I know myself. But I want to import the table without to change the Windows
settings manually.

Bye.

--
gilgil



"AA2e72E" ha scritto nel messaggio
...
The decimal symbol is set he
Start | Settings | Control Panel | Regional Options. Have a look at the
Numbers tab.

"gilgil" wrote:

Windows XP Home + Excel 2000

How to change decimal sign from "." to "," in importing text from web
tables?

VBA - How to change the decimal sign
 

On Wed, 9 Mar 2005 10:14:22 +0100, "gilgil" wrote:

Windows XP Home + Excel 2000

How to change decimal sign from "." to "," in importing text from web
tables?
The system setting in my computer for decimal sign is "," and not "."

Thanks.

If the values are being imported as TEXT, then you could do Edit/Replace or use
the SUBSTITUTE worksheet function. Then you could convert them to numbers.

If the values are being imported as numbers, then give some examples as to the
actual and desired values when they are in Excel.


--ron

VBA - How to change the decimal sign
 

Example of web table before importation:
aaa 1.90
bbb 2.00
ccc 1.80

Desired values when they are in Excel:
aaa 1,90
bbb 2,00
ccc 1,80


If I could import the web table as text than I could replace the digits with
comma (Edit/Replace or fonction SUSTITUTE).
Before that I can make that Excel recognazes the numbers as values and
insert in the cells wrong values.

Is it possible to prevent Excel recognizing numbers as values?
Thanks.
--
gilgil

Excuse my english


"Ron Rosenfeld" ha scritto nel messaggio
...
On Wed, 9 Mar 2005 10:14:22 +0100, "gilgil"
wrote:


If the values are being imported as TEXT, then you could do Edit/Replace
or use
the SUBSTITUTE worksheet function. Then you could convert them to
numbers.

If the values are being imported as numbers, then give some examples as to
the
actual and desired values when they are in Excel.

VBA - How to change the decimal sign
 

On Wed, 9 Mar 2005 20:33:32 +0100, "gilgil" wrote:

Example of web table before importation:
aaa 1.90
bbb 2.00
ccc 1.80

Desired values when they are in Excel:
aaa 1,90
bbb 2,00
ccc 1,

But when you import the web table into Excel, what is the value in Excel AFTER
importation. In other words, how does Excel, with your regional settings,
interpret 1.90?


--ron

VBA - How to change the decimal sign
 

on the Web in Excel

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

Thanks.

--
gilgil




"Ron Rosenfeld" ha scritto nel messaggio
...
On Wed, 9 Mar 2005 20:33:32 +0100, "gilgil"
wrote:

Example of web table before importation:
aaa 1.90
bbb 2.00
ccc 1.80

Desired values when they are in Excel:
aaa 1,90
bbb 2,00
ccc 1,

But when you import the web table into Excel, what is the value in Excel
AFTER
importation. In other words, how does Excel, with your regional settings,
interpret 1.90?


--ron

VBA - How to change the decimal sign
 

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