Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Place goes crazy when using the "&" Sign. | Excel Discussion (Misc queries) | |||
Plus sign after decimal??? | Excel Discussion (Misc queries) | |||
how can i change dollar sign to rupee sign in sales invoice | Excel Discussion (Misc queries) | |||
can I change the $ sign to a £ sign in an MS template? | Excel Worksheet Functions | |||
How do you change the sign (e.g. +3 to -3 OR -3 to +3) | Excel Discussion (Misc queries) |