VBA - How to change the decimal sign

[gilgil]

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.

 

[Guest]

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

 

[gilgil]

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

Bye.

 

[Ron Rosenfeld]

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

 

[gilgil]

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.

 

[Ron Rosenfeld]

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

 

[gilgil]

on the Web in Excel

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

Thanks.

 

[Ron Rosenfeld]

on the Web in Excel

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

Thanks.

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