Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma to period
Dear clever folks!
I'm living in Sweden where we use komma instead of period as seperator for the decimal part in numbers. This is no good since all (almost) programes uses period as a seperator. I'm doing some number manipulations in Excel and at the end I'm suppose to export my data as a text file, but Excel exports it with a komma instead of period, which my next programe cannot interpret. I know that changing my regional settings within Windows solves this problem, but I don't have administrative privilgies on the computer so that is of no good. I have done a basic macro that looks like this: Sub ConvertCommaToPeriod() Worksheets(2).Columns("B").Replace _ What:=",", Replacement:=".", LookAt:=xlPart ', _ 'SearchOrder:=xlByColumns ', MatchCase:=True End Sub This works well for data that looks like this: '1234,5678 (notice the apostrophe in the beginning!) But fails to recognise 1234,5678 (cell formated as text) What is up with this? I could of course insert an apostrophe in the beginning of every cell, but surely there must be a better way? Best regards Freddie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma to period
On the Tool |Option International tab there is a box marked Use System
Separators. If you uncheck this you can fill in what you want for decimal and thousands separators. I live in Canada so I normally have numbers like 12.34. Using this method I was able to get 12,34 without changing the Regional Setting. You just need to do the reverse - use dot and comma. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Freddie" wrote in message om... Dear clever folks! I'm living in Sweden where we use komma instead of period as seperator for the decimal part in numbers. This is no good since all (almost) programes uses period as a seperator. I'm doing some number manipulations in Excel and at the end I'm suppose to export my data as a text file, but Excel exports it with a komma instead of period, which my next programe cannot interpret. I know that changing my regional settings within Windows solves this problem, but I don't have administrative privilgies on the computer so that is of no good. I have done a basic macro that looks like this: Sub ConvertCommaToPeriod() Worksheets(2).Columns("B").Replace _ What:=",", Replacement:=".", LookAt:=xlPart ', _ 'SearchOrder:=xlByColumns ', MatchCase:=True End Sub This works well for data that looks like this: '1234,5678 (notice the apostrophe in the beginning!) But fails to recognise 1234,5678 (cell formated as text) What is up with this? I could of course insert an apostrophe in the beginning of every cell, but surely there must be a better way? Best regards Freddie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract the text between last comma and last but one comma. | Excel Worksheet Functions | |||
Comma Delimited-need comma at beginnng & end | Excel Discussion (Misc queries) | |||
excel 2000 takes comma as period | Excel Discussion (Misc queries) | |||
Batch converting CSV files from comma-decimal to period-decimal | Excel Discussion (Misc queries) | |||
Period to Period percentage change? | Excel Discussion (Misc queries) |